Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Shaan.

Asked: January 08, 2018 - 7:24 am UTC

Last updated: January 10, 2018 - 12:11 am UTC

Version: 11

Viewed 1000+ times

You Asked

Hi Tom,

I have a requirement where I need to change dbms_job functionality to dbms_scheduler.Regarding that I have few query :
1.The existing dbms_job.submit_job is called within loop which calls certain procedure
ex: for I in 1..10
dbms_job.submit(jobnumber,action,sysdate).
Can we use same ; call dbms_scheduler within loop as well.If yes,then is it advisable.

2.Dbms_job.submit ends after completing its function.In scheduler do we need to use auto_drop "TRue" to get the same functionality.

3.Existing code checks for the failure job and calls the broken function of dbms_job.What will be the alternative for this in scheduler.

Thanks In advance.

and Connor said...

1) In the scheduler, a job is an object that is owned by the schema (like any other objects) so it must have a unique name. So you could do something like:

for i in 1 .. 10 loop
  dbms_scheduler.create_job('MYJOB'||i, .... );
end loop;


2) If a scheduler job does not have a repeat interval, it will be dropped just like dbms_job, eg

SQL> begin
  2      dbms_scheduler.create_job (
  3         job_name           =>  'MYJOB',
  4         job_type           =>  'PLSQL_BLOCK',
  5         job_action         =>  'null;',
  6         start_date         =>  sysdate,
  7         enabled            =>  true,
  8         comments           =>  'One off job');
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> select job_name from user_scheduler_jobs;

JOB_NAME
--------------------------------------------------------------
MYJOB

SQL> -- a short time later
SQL> /

no rows selected



3) There is a lot more flexibility with the scheduler. The concept of a job retrying and ultimately no longer running is still there, but there is a lot of other options as well in terms of monitoring them, or reacting to a failure. For example, you can be emailed on an error, or you can have the scheduler raise an "event" when a job fails, which could trigger other jobs etc.

Check out the details here

https://docs.oracle.com/cd/E18283_01/server.112/e17120/scheduse008.htm


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Warning! Commit behavious is different

Kim Berg Hansen, January 09, 2018 - 10:19 am UTC

Just a word of warning - dbms_scheduler.create_job will implicitly commit transaction. (Think of it as DDL, more like a "CREATE JOB MYJOB1 AS ...")

So if the original loop doing a lot of dbms_job.submit calls is part of a transaction, you might have problems converting to dbms_scheduler.create_job.

Just something to beware of.
Connor McDonald
January 10, 2018 - 12:11 am UTC

Good point.

One of the biggest shortfalls (in my opinion) of scheduler is the lack of a transactional interface.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database