Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Running ad-hoc jobs in Oracle (Read 6414 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Running ad-hoc jobs in Oracle
23.10.06 at 16:46:16
 
Running ad-hoc jobs in Oracle
 
In Oracle the dbms_jobs package can be used to schedule and run jobs on the database server. To create and run an ad-hoc job you can use the following PL/SQL. It will create a job and run it. Then the job will be deleted from the schedule again (removed from dba_jobs).
 
declare
 job binary_integer;
begin
 dbms_job.submit(job,'package.procedure;');
 dbms_job.run(job);
end;
Back to top
 
« Last Edit: 25.10.06 at 08:50:53 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Running ad-hoc jobs in Oracle
Reply #1 - 25.10.06 at 08:33:52
 
Experimenting further with the running of these jobs I ran into the following error.
 
ORA-00164: autonomous transaction disallowed within distributed transaction
 
You may not be in a transaction before running a job like discribed. Do a commit (or rollback) immediately before and after the running the ad-hoc job.
 

declare
 job binary_integer;
begin
 commit;
 dbms_job.submit(job,'package.procedure;');
 dbms_job.run(job);
 commit;
end;
Back to top
 
« Last Edit: 25.10.06 at 08:50:32 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Running ad-hoc jobs in Oracle
Reply #2 - 19.08.08 at 13:29:34
 
Another example:
 
declare
  l_job       number;
  l_what      varchar2(1000) :=
'begin
   package.procedure(123);
 end;';
  l_next_date date := sysdate + 1/(24*60*60);
begin  
  dbms_job.submit( job       => l_job
                , what      => l_what
                , next_date => sysdate + 1/(24*60*60)
                );
end;
Back to top
 
« Last Edit: 19.08.08 at 13:29:52 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1