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 ;',
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


From the column zmyxmjz