Enables the scheduling and execution of PL/SQL programs.
Submit is the procedure of DBMS_JOB package.
The parameters of Submit procedure are —
JOB – Unique identifier of the job (Out parameter)
WHAT – PL/SQL code to execute as a job
NEXT_DATE – Next execution date of the job
INTERVAL – Date function to compute the next execution date of a job.
Every next day a new record should get added in the table djt1.
Create table djt1(a number);
Create sequence jobseq;
Create or replace procedure p1
Insert into djt1
Variable jobno number
job => :jobno,
what => ‘P1;’,
next_date => trunc(sysdate),
interval => ‘trunc(sysdate 1)’
In the init.ora file JOB_QUEUE_PROCESSES = n has to be setted
n can be between 1 and 36.
Change the system date to the next date. The t1 table gets the next value of the sequence.
Again repeat the process.
Imp setting – job_queue_processes = n (n between 1 and 36) in the init.ora file
Select job,what from user_jobs;
Exec dbms_job.Broken(1,true) – 1 is job no
Dbms_job.change(jobno, what, next_date, interval)
Jobno cannot be changed.
If there is no change for what, next_date, interval then specify NULL
For having interval of 60 seconds it will be ‘Sysdate (59/ (24*60*60))’