DBMS_JOB Package

Oracle supplied packages -DBMS_JOB Package

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.

Example –
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
Is
Begin
Insert into djt1
Values(jobseq.nextval);
End;
/

Variable jobno number

begin
dbms_job.submit
(
job => :jobno,
what => ‘P1;’,
next_date => trunc(sysdate),
interval => ‘trunc(sysdate 1)’
);
commit;
end;

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
Exec dbms_job.Remove(1)

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))’

Posted in Uncategorized