Oracle Example-2

The following example submits three jobs to the job queue, numbered 1, 2, and 3. Job 1 passes a string and number into procedure my_job1, runs it in one hour, and executes it every day thereafter. Job 2 passes a date into procedure my_job2, executes for the first time tomorrow, and execute it every 10 minutes thereafter. Job 3 is a PL/SQL block that does nothing, executes immediately, and will be removed from the queue automatically.

BEGIN

   DBMS_JOB.ISUBMIT

      (job  => 1

      ,what => 'my_job1(''string_parm_value'',120);'

      ,next_date => SYSDATE + 1/24

      ,interval => 'SYSDATE +1');

   DBMS_JOB.ISUBMIT

      (2, 'my_job2(date_IN=>SYSDATE);'

        ,SYSDATE+1,'SYSDATE+10/1440');

   DBMS_JOB.ISUBMIT(3,'BEGIN null; END;',SYSDATE,null);

END;

The ISUBMIT procedure allows the calling user or application to decide the job identification number.Collisions in job numbers will result in the unique constraint violation noted earlier. Therefore, it is probably better not to embed fixed job numbers into applications (as this will increase the chances for collisions) and to use SUBMIT instead of ISUBMIT

Advertisement