Create Job In Oracle

A user with a Create any job privilege can create a job in any schema except the sys schema. Attaching a job with a particular class requires the execute privileges for that class.
Oracle database 10g offers a collection a rich set of functionality for complex scheduling tasks.
The component that causes something to be executed at a specified time is called a Job.


We can use DBMS_SCHEDULER.CREATE_JOB procedure of the Dbms_scheduler package to create a job
which is in disabled state by default.
A job becomes running and scheduled when it is explicitly enabled.


Example: - How to create a job, For this you provide a name in the form [schema.]name and also need privileges from the sysdba.


1. Connect to your database as user SYSTEM using SQL*Plus.


2. Create a table to store times, and set your date format to show the date and
time.


SQL> create table times (c1 date);


SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';


3. Create a job to insert the current time into the table every minute.


SQL> begin
dbms_scheduler.create_job(
job_name=>'savedate',
job_type=>'plsql_block',
job_action=>'insert into times values(sysdate);',
start_date=>sysdate,
repeat_interval=>'freq=minutely;interval=1',
enabled=>true,
auto_drop=>false);
end;
/


PL/SQL procedure successfully completed.


4. Query the job table to see that the job is scheduled.


SQL> select job_name,enabled,to_char(next_run_date,'dd-mm-yy hh24:mi:ss'),run_count


from user_scheduler_jobs;


JOB_NAME ENABL TO_CHAR(NEXT_RUN_ RUN_COUNT
------------------------- ----- ----------------- ----------
SAVEDATE TRUE 15-01-05 14:58:03 2


5. Query the times table to demonstrate that the inserts are occurring.


SQL> select * from times;


6. Disable the job.


SQL> exec dbms_scheduler.disable('savedate');


7. Re-run the queries from Steps 4 and 5 to confirm that the job is disabled, and
that no more inserts are occurring.
8. Drop the job:
SQL> exec dbms_scheduler.drop_job('savedate');