How to create a job in Oracle "job" and explore the details

the job can be used in Oracle to automate some tasks, similar to the functionality of the UNIX operating system crontab command.
A brief demonstration, for reference.

1. Create a table T, which contains an X field, defined as a date type, to facilitate subsequent scheduled task testing.
[Email protected]ora10g> CREATE table t (x date);

Table created.

2. Create a stored procedure p_insert_into_t, each time the stored procedure is executed, inserts a bar into the T tablesystemCurrenttime.
[Email protected]ora10g> Create or Replace procedure p_insert_into_t
2 AS
3 begin
4 INSERT INTO T
5 values (sysdate);
6 end;
7/

Procedure created.

3.OK, ready, let's create a job, and this job will run every minute? Need to pay attention to a detail!
[Email protected]ora10g> variable job_number number;
[Email protected]ora10g> Begin
2 Dbms_job.submit (: Job_number,
3 ' p_insert_into_t; ',
4 Sysdate,
5 ' sysdate+1/(24*60) ');
6 end;
7/

PL/SQL procedure successfully completed.

4. We look at the job information created through the User_jobs view.
[Email protected]ora10g> select Job,
2 Log_user,
3 To_char (last_date, ' yyyy-mm-dd hh24:mi:ss ') last_date,
4 To_char (next_date, ' yyyy-mm-dd hh24:mi:ss ') next_date,
5 interval,
6 What
7 from User_jobs
8/

JOB log_user last_date next_date INTERVAL What
------- -------- ------------------- ------------------- ----------------- ----------------
SEC 2010-01-29 00:34:20 sysdate+1/(24*60) p_insert_into_t;

Where the details are, the last_date content here is empty, indicating that the job has not been executed, so the job will never be executed automatically.
This can be verified from the T table without data:
[Email protected]ora10g> select * from T;

No rows selected

So, how do you make it work automatically?
It's simple, as long as we manually execute the job.

5. Manually perform the job once to make it in accordance with establishedTimeinterval execution.
[Email protected]ora10g> Execute Dbms_job.run (27);

PL/SQL procedure successfully completed.

A data with the current time will be inserted in the T table.
[Email protected]ora10g> select * from T;

X
-------------------
2010-01-29 00:37:42

Review the job information again
[Email protected]ora10g> select Job,
2 Log_user,
3 To_char (last_date, ' yyyy-mm-dd hh24:mi:ss ') last_date,
4 To_char (next_date, ' yyyy-mm-dd hh24:mi:ss ') next_date,
5 interval,
6 What
7 from User_jobs
8/

JOB log_user last_date next_date INTERVAL What
------- -------- ------------------- ------------------- ----------------- ----------------
SEC 2010-01-29 00:37:42 2010-01-29 00:38:42 sysdate+1/(24*60) p_insert_into_t;

At this point last_date shows the time we performed the job, while Next_date shows the time the next job will be executed. The job will be executed every other minute thereafter.
The contents of the T table after automatic execution for some time are as follows:
[Email protected]ora10g> select * FROM t order by X;

X
-------------------
2010-01-29 00:37:42
2010-01-29 00:38:46
2010-01-29 00:39:46
2010-01-29 00:40:46
2010-01-29 00:41:46
2010-01-29 00:42:46
2010-01-29 00:43:46
2010-01-29 00:44:46
2010-01-29 00:45:46
2010-01-29 00:46:46
2010-01-29 00:47:46
2010-01-29 00:48:46
2010-01-29 00:49:46
2010-01-29 00:50:46
2010-01-29 00:51:46
2010-01-29 00:52:46

Rows selected.

6. Why does the job that you just created can't be executed automatically?
This is an inadvertent result!
When creating the job, you need to specify "COMMIT;" At the end! Indicates that the creation is done once.
Delete the previous job and recreate a new job with a "COMMIT" statement.
[Email protected]ora10g> variable job_number number;
[Email protected]ora10g> Begin
2 Dbms_job.submit (: Job_number,
3 ' p_insert_into_t; ',
4 Sysdate,
5 ' sysdate+1/(24*60) ');
6 commit;
7 End;
8/

[Email protected]> print job_number;

Job_number
----------
29

The job information created here is as follows, and it is visible that Last_date has been created after the creation of the content, indicating that it has been executed once.
[Email protected]ora10g> select Job,
2 Log_user,
3 To_char (last_date, ' yyyy-mm-dd hh24:mi:ss ') last_date,
4 To_char (next_date, ' yyyy-mm-dd hh24:mi:ss ') next_date,
5 interval,
6 What
7 from User_jobs
8/

JOB log_user last_date next_date INTERVAL What
------- -------- ------------------- ------------------- ----------------- ----------------
$ SEC 2010-01-29 01:02:11 2010-01-29 01:03:11 sysdate+1/(24*60) p_insert_into_t;

After a minute you can see that there are two records in the T table.
[Email protected]ora10g> select * from T;

X
-------------------
2010-01-29 01:02:11
2010-01-29 01:03:11

7. Delete the Job method
Very simple, use "Dbms_job.remove" can.
[Email protected]ora10g> Execute dbms_job.remove (29);

PL/SQL procedure successfully completed.

8. Finally, talk about the parameters used to create the job.
1) using the DESC command to view the Dbms_job, you can get a list of arguments for this stored procedure.
[Email protected]ora10g> Desc dbms_job
...
PROCEDURE SUBMIT
Argument Name Type in/out Default?
------------------------------ ----------------------- ------ --------
JOB Binary_integer out
What VARCHAR2 in
Next_date DATE in DEFAULT
INTERVAL VARCHAR2 in DEFAULT
No_parse BOOLEAN in DEFAULT
INSTANCE Binary_integer in DEFAULT
Force BOOLEAN in DEFAULT
...

2) If you wish to have a better understanding of these parameters, you can refer to Oracle's official documentation for detailed and thoughtful details.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_job.htm#sthref2936

3) Focus on an example of the interval parameter in the official documentation
' Sysdate + 7 ' means to execute once a week;
' Next_day (sysdate, ' Tuesday ') ' means to execute once every Tuesday;
' null ' means to execute only once.

In this article, I'm using ' sysdate+1/(24*60) ' to indicate that it is executed every minute. Very image, One-twenty Fourth of the day is an hour, an hour of one-sixtieth is the meaning of a minute.

9. Summary
Through this article and everyone to share a little about job creation method and use, hope to everyone helpful.
Don't miss the details!

Good luck.

Secooler
10.01.28

--The End--

How to create a job in Oracle "job" and explore the details