"Go" Oracle Custom scheduled execution tasks

This section summarizes: This section describes the use of Oracle's own jobs to enable Oracle custom timed execution tasks.

1. Introduction

Custom scheduled tasks are performed in two forms, system level and database level,

From the operating system level,

Windows systems we can use task plans to implement,

For the WinXP system, set the steps below, start---setup---Control Panel---Task schedule, then add the task schedule, and follow the wizard to complete the configuration;

For the Win7 system, set the steps below, click Start, then enter [Task Scheduler] in the Search Programs and files box, then click Out the Task Scheduler, create a basic task;

Linux systems we can use the crontab command to be implemented,

The use of the crontab command can be seen on previous blogs (http://www.cnblogs.com/java-pan/archive/2012/07/23/linux.html);

From the database level, we can use the database job to achieve;

This section focuses on customizing a simple scheduled execution task with the Oracle database job. This section uses Oracle to customize a timer to insert statements into a table.

Here's why I'm doing this Oracle timed task:

In the project to synchronize the data of some tables in one database to another database at a fixed time, obviously I can't do it myself every time.

So, what am I going to do? At this point, Oracle's scheduled job assignment is definitely my best choice. I put the synchronized script in a stored procedure,

Then it is OK to execute the stored procedure at a fixed time.

Note: All of the following actions are performed under the sytem user. Client login with PL/SQL.

2. Project Environment

Install oracle10g, then install PL/SQL, and then log in with the Administrator user system,

Open the SQL statement in the SQL window input graph, and the details of the operating environment are as follows:

3. Environmental preparedness

(1) Create a new table, the command is test, the table statement is as follows:

Create Table system.test0916 (   number(2PRIMARYKEY,   seqtime  DATE)

(2) Create a new stored procedure named Test_proc with the following statements for the stored procedure:

CREATE OR REPLACE PROCEDURETest_proc isBEGIN  INSERT  intoSYSTEM. TEST0916 (SEQ, Seqtime)VALUES(NVL (SELECT MAX(SEQ) fromSYSTEM. TEST0916)+ 1,0), sysdate); COMMIT; EXCEPTION whenOTHERS ThenDbms_output. Put_Line ('Exception Happened,data was rollback'); ROLLBACK;END;

Execute the above stored procedure in the SQL Windows window and after execution, look at the procedures menu under Objects to see the new stored procedure

The icon in front of Test_proc does not have a red mark, indicating that the stored procedure is normal;

(3) Check Oracle's job run environment

All I've got here is to check the number of processes for Oracle's job queue, and if 0 means no job is running,

Although it is generally said to be 10 by default, I do not know why I am 0 when i install Oracle.

To view the number of processes:

Modify the number of processes (if you are 0, you need to modify the following statement):

I have changed here to 10, if I want to change 11来 to simulate the following operation;

In the PL/SQL command window, enter the following statement:

Alter set job_queue_processes=ten scope=both;

Run as follows:

After the modification, we look at the following:

See, I'm changing to 11.

Additional Notes :

On the Internet some said that modify the number of processes can modify the Oracle initial file, at first I was to find the location of the initial file, and then open with notepad++ to modify,

After saving I restart the Oracle service and the computer, but found that at this time with PL/SQL landed in Oracle, has been reported that the specified string is not recognized, here to explain this does not work.

Here's a question, how do we find the location of the Oracle initial file? You can see this in the Command Window window of PL/SQL, by executing the following statement:

I was using notepad++ to modify the contents of the shaded section:

*.job_queue_processes=11

4. Custom Timed execution tasks

Suppose we want to insert a record into the table test0916 every two minutes, here we only need to execute the stored procedure Test_proc every 2 minutes.

In PL/SQL, Command window, enter the following statement:

var  Number ; begin Dbms_job.submit (: job_num,'test_proc; ', sysdate,'sysdate+2/24/60'); End;

The execution process is as follows:

After execution, remember to click the PL/SQL submit button;

5. View execution Results

(1) See if our new job was successful

You can see the last record by looking at the What field in the Dba_jobs table even though we have just built a new scheduled task;

(2) See if the test0916 table has data inserted

6. Other relevant instructions

(1) How do I delete a job?

Execute the following statement in the PL/SQL command window:

exec Dbms_job.remove (81);

Note: The number 81 here is the value of the job field that corresponds to the row of the job record currently being deleted in the Dba_jobs table;

(2) What is the meaning of each parameter of the submit?

Using the Submit () process, the work is properly planned.
This process has five parameters: job, what, next_date, interval, and No_parse.
PROCEDURE Submit (Job out Binary_ineger,
What in Varchar2,
Next_date in Date,
Interval in varchar2,
No_parse in Booean:=false)
The job parameter is the Binary_ineger returned by the submit () procedure. This value is used to uniquely identify a job. There is a double quotation mark in front of it, it is not shown in SQL Windwo, the Copy to command window can be displayed;
What parameter is the PL/SQL code block that will be executed. Here is the name of the stored procedure, followed by a semicolon in the English state can not be dropped;
The Next_date parameter indicates when the job will run.
Interval parameter when this work will be re-executed. Here the plus, parentheses must be entered in the English state;
The No_parse parameter indicates whether this work should be parsed at the time of submission or execution--true indicates that this PL/SQL code should be parsed when it first executes, and false indicates that this PL/SQL code should parse immediately.

(3) How is the time interval of timed execution set?

Here everyone can go to Baidu, online search generally can find to meet your needs, mainly results trunc function to achieve.

"Go" Oracle Custom scheduled execution tasks