Akdora’s Blog

Programming, Oracle, Life, Fun

Oracle Example-2 January 9, 2007

Filed under: Oracle — Akdora @ 1:53 am

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

 

One Response to “Oracle Example-2”

  1. I definitely enjoyed reading this write-up.Thanks.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s