Create a scheduled job in Oracle

On Windows, you can set a task to start some tasks at regular intervals, for example, once a week or once a day. I also saw a lot of applications write PL/SQL batch in Oracle, and then use Windows task to start these batch, which is quite tedious. In addition, Oracle is so powerful, why does it have to start batch using the task method provided by windows? In addition, the batch time setting in windows is not very flexible and cannot be expressed flexibly by computing. So, why don't we use the job feature that comes with Oracle.

 

The sys. dbms_job.submit package in Oracle provides the task function. First we write a PL/SQL for execution, for example, test_sp, and then create a task using SYS. dbms_job.submit.

Declare <br/> X number; <br/> begin <br/> sys. dbms_job.submit <br/> (job => x <br/>, what => 'test _ sp; '<br/>, next_date => to_date ('2014/1/2 00:00:00 ', 'dd/mm/yyyy hh24: MI: ss') <br/>, interval => 'sysdate + 15/60/24' <br/>, no_parse => false <br/>); <br/> sys. dbms_output.put_line ('job number is: '| to_char (x); <br/> commit; <br/> end; <br/>

In this way, you can view the job in the user_jobs view.

Select job, next_date, next_sec, failures, broken from user_jobs;

 

However, after some databases are set up, they find that the batch time has not been executed. Why? The answer is database settings. One parameter in the database is job_queue_processes. If this value is set to 0, the job cannot be run automatically. In the database installed by default, this value is generally 10. If you find that the number is 0, you can manually change it. The SQL statement is as follows:

Show parameter job_queue_processes; (query the job_queue_processes value)

Alter system set job_queue_processes = 10; (update job_queue_processes value)

 

Of course, if you want to set this parameter, the user's permissions are required. If you do not have the permission, let's talk about it with dBA and set job_queue_processes to a non-0 parameter.