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))’
Discussion about this article
| Author | Body |
admin
8/24/2009 3:21 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|