The simplest scheduled Oracle task

The simplest scheduled Oracle task
1. Create a table in PLSQL:
Create Table hwqy. Test
(
Carno varchar2 (30 ),
Carinfoid number
)

2. Create a stored procedure in PLSQL:
Create or replace procedure pro_test
As
Carinfo_id number;
Begin
Select s_carinfoid.nextval into carinfo_id
From dual;
Insert into test (test. carno, test. carinfoid) values (carinfo_id, '123 ');
Commit;
End pro_test;

3. Start the task in the SQL command window:
Run the following command after SQL>:
Variable jobno number;
Begin
Dbms_job.submit (: jobno,
'Pro _ test ;',
Sysdate, 'sysdate + 1/24/12 ');

Commit;
End;
/

Prompt after submission:
PL/SQL procedure successfully completed
Jobno
---------
1

4. Track the task status (view the task queue ):

SQL> select job, next_date, next_sec, failures, broken from user_jobs;

Job next_date next_sec failures broken
-----------------------------------------------------
1 2008-2-22? 01:00:00 0 n

This indicates that a task exists.
Run select * from test t to view the scheduled task results. It can be seen that the scheduled task is executed normally.

5. Stop started scheduled tasks:
First Run select job, next_date, next_sec, failures, broken from user_jobs;
To view the job number of a scheduled task.
Run the following statement in SQL> to stop a started scheduled task:
Begin
Dbms_job.remove (1 );
Commit;
End;
/
Stop a job with job 1.
After execution, the following information is displayed:
PL/SQL procedure successfully completed

6. view the number of processes:
Show parameter job_queue_processes;
Must be greater than 0; otherwise, execute the following command to modify:
Alter system set job_queue_processes = 5;

7. Create another task (executed every 5 minutes ):
Variable jobno number;

Begin
Dbms_job.submit (: jobno, 'Pro _ test ;',
Sysdate, 'sysdate + 1/24/12 ');
Commit;
End;
/

After a scheduled task is created, view the job in PLSQL. Its SQL statements are similar to the following:

begin
sys. dbms_job.submit (job =>: jobno,
what => 'Pro _ test; ',
next_date => to_date ('21-02-2008 17:37:26 ', 'dd-mm-yyyy hh24: MI: ss'),
interval => 'sysdate + 1/24/12');
commit;
end;
/

Therefore, the complete format for creating a task is:
Variable jobno number;
Begin
SYS. dbms_job.submit (job =>: jobno,
What => 'Pro _ test ;',
& Nbsp; next_date => to_date ('21-02-2008 17:37:26 ', 'dd-mm-yyyy hh24: MI: ss '),
Interval => 'sysdate + 1/24/12 ');
Commit;
End;
/
-- The system automatically assigns a job no. jobno.

8. Execute select job, next_date, next_sec, failures, broken from user_jobs;
Result:
Job next_date next_sec failures broken
1 1 am 01:00:00 0 n
2 2 05:42:45 17:42:45 0 n
3 3 05:42:45 17:42:45 0 n

All of the above processes passed the test at oracle817.

 

Http://www.th7.cn/Article/sj/ora/200911/20091112201112_2.html