Oracle build job periodically runs stored procedures

1 View First


Sql> Show Parameter Job

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Job_queue_processes Integer 10

If the value is 0 needs to be modified (if 0 does not run the job, this value is the number of jobs allowed to run concurrently)

alter system set job_queue_processes = 10; --or other value

2 A simple example:
　　
Create a test table
Sql> CREATE TABLE TEST (a date);
　　
The table is created.
　　
Create a custom procedure
sql> Create or replace procedure MYPROC as
2 begin
3 INSERT into TEST values (sysdate);
4 End;
5/
　　
The process has been created.
　　
Create Job
sql> variable JOB1 number;
Sql>
Sql> begin
2 Dbms_job.submit (: Job1, ' MYPROC; ', sysdate, ' sysdate+1/1440 '); --1440 minutes per day, or one minute to run the test procedure once
3 END;
4/
　　
The PL/SQL process has completed successfully.
　　
Run Job
Sql> begin
2 Dbms_job.run (: JOB1);
3 END;
4/
　　
The PL/SQL process has completed successfully.
　　
Sql> Select To_char (A, ' yyyy/mm/dd hh24:mi:ss ') time from TEST;
　　
Time
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24
　　
Delete Job
Sql> begin
2 Dbms_job.remove (: JOB1);
3 END;
4/
　　
The PL/SQL process has completed successfully.
Sql> print:n;
The system prints the number of this task, for example, the result is 300.
As above, we have created a task with a task number of 300 that executes every 1 minutes. You can view the execution of this task by user_jobs the data dictionary provided by Oracle:
Sql> select Job,next_date,next_sec,failures,broken from User_jobs;
The results of the implementation are as follows:
Job Next_date next_sec Failures broken
2000/10/10 11:45:15 0 N
This indicates that the task number is 300, and the next time it is executed on 2000/10/10 11:45:15, the execution of this task is logged 0 times. Note: When an error occurs in the execution job, Oracle logs it in the log, and the number of failures is automatically added to 1 each time. When the number of execution failures reaches 16, Oracle flags the job as broken. Thereafter, Oracle no longer continues to execute it until the user invokes the procedure Dbms_job.broken, sets it back to not broken, or forces the call to Dbms_job.run to re-execute it.
In addition to the submit stored procedures we discussed above, Oracle provides many other stored procedures to manipulate tasks. For example: Dbms_job.change, Dbms_job.what, Dbms_job.interval can be used to modify the submitted task. To delete the task, simply run Dbms_job.remove (n), where n is the task number.

3 The above example does not refer to the stored procedure with the parameters of the problem, if the parameters are assumed to be
MYPROC (Ina in VARCHAR2,
------------------The following is required to output a parameter group---------------------------------
Outa out number,
Outb out VARCHAR2)

In the code that creates the job
Create Job
Begin
Sys.dbms_job.submit (Job = job,
what = '
Declare

Outa number; --Define output variables
Outb VARCHAR2 (40);
Begin
MYPROC (' input ', Outa =>outa, Outb =>OUTB);
End; ',
Next_date = To_date (' 10-10-2006 08:48:34 ', ' dd-mm-yyyy hh24:mi:ss '),
Interval = ' sysdate+1 ');
Commit
End
/

trackback:http://tb.blog.csdn.net/trackback.aspx?postid=1399765

This article from: Sina blog http://blog.sina.com.cn/s/blog_586f1baf01000bww.html

Oracle build job periodically runs stored procedures