Job Scheduler in oracle

Job scheduler:-

Scenario:-

When you want to execute any procedure or any job at a particular time or for repeated times, you can use various windows command tasks in windows or any other third party software’s. While using windows command and other tools we have to specify all the information like database user name, password and database name. This will cause a problem, if you have a password change policy. Suppose if you have a policy to change password in every 1 month then every month you have to go the windows command and change the password manually. but in oracle you don’t have to do it manually. Oracle has its own job scheduler which runs on the given time and the advantage is that, it doesn’t require any database name, database user name and password since we create it in the schema where our procedure resides.

What is oracle dbms_scheduler?

Dbms_scheduler.create_job package will allow you to create a job which will fire at a given time. Suppose you need to execute a procedure at 3 o’clock, you can schedule a job and specify the name of the procedure which needs to be executed; oracle job scheduler will execute it automatically. It’s very simple and flexible solution to schedule jobs.

Syntax for creating a job scheduler is:-

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

JOB_NAME            =>      'NAME_OF_THE_JOB,

JOB_TYPE             =>      'PLSQL_BLOCK/ PROCEDURE',

JOB_ACTION         =>      'BLOCK_NAME; END;’,

START_DATE        =>      SYSTIMESTAMP,

END_DATE            =>      NULL / IF_YOU_WANT_TO_PUT_ANY_DATE,

COMMENTS          =>      'ANY_COMMENT_YOU_WANT_TO_SPECIFY.’

ENABLED              =>      TRUE / FALSE,

REPEAT_INTERVAL    =>      'FREQ=MONTHLY / DAILY; BYDAY=; BYHOUR=’; BYMINUTE=’; BYSECOND=’;);

END;

Let’s try to create a job which will trigger on 1^ST day of every month at 10 am:-

Suppose my procedure name is PROC_TEST:-

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

JOB_NAME           =>      'MONTHLY_TESTING_JOBS',

JOB_TYPE             =>      'PLSQL_BLOCK',

JOB_ACTION       =>      'BEGIN PROC_TEST; END;',

START_DATE      =>      SYSTIMESTAMP,

END_DATE         =>      NULL,

COMMENTS      =>      'JOB IS CREATED FOR TESTING PURPOSE.’,

ENABLED          =>      TRUE,

REPEAT_INTERVAL    =>      'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=10');

END;

Now, try to create a job which will fire daily basis:-

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

JOB_NAME           =>      'NAME_OF_THE_JOB',

JOB_TYPE           =>      'PLSQL_BLOCK / PROCEDURE',

JOB_ACTION       =>      'BEGIN BLOCK_NAME; END;',

START_DATE      =>      SYSTIMESTAMP,

END_DATE          =>      NULL,

COMMENTS        =>      'COMMENT THAT YOU WANT TO SPECIFY.',

ENABLED            =>      TRUE,

REPEAT_INTERVAL    =>      'FREQ=DAILY; BYDAY=MON, TUE, WED, THU, FRI, SAT, SUN; BYHOUR=TIME; BYMINUTE=MINUTES; BYSECOND=SECOND ;');

END;

Let’s try to create a job which will trigger daily at 11 am:-

Suppose my procedure name is PROC_TEST:-

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

JOB_NAME           =>      'DAILY_TESTING_JOB',

JOB_TYPE            =>      'PLSQL_BLOCK',

JOB_ACTION        =>      'BEGIN PROC_TEST; END;',

START_DATE        =>      SYSTIMESTAMP,

END_DATE            =>      NULL,

COMMENTS           =>      'JOB IS CREATED FOR TESTING PURPOSE.',

ENABLED                =>      TRUE,

REPEAT_INTERVAL    =>      'FREQ=DAILY; BYDAY=MON, TUE, WED, THU, FRI, SAT, SUN; BYHOUR=11; BYMINUTE=00; BYSECOND=0 ;');

END;

If everything is correct then the above created jobs will execute on given time. But in case you need to execute them manually, you can execute it by using dbms_scheduler_run_job oracle supplied package.

Syntax to run job manually:-

EXEC DBMS_SCHEDULER.RUN_JOB (JOB_NAME => 'NAME_OF_THE_JOB');

Suppose, we want to execute DAILY_TESTING_JOB, we can execute it like:-

EXEC DBMS_SCHEDULER.RUN_JOB (JOB_NAME => 'DAILY_TESTING_JOB');

Dropping jobs:-

If we don’t require job anymore then we can drop it by using dbms_scheduler.drop_job oracle supplied package.

Syntax for dropping job is:-

BEGIN

DBMS_SCHEDULER.DROP_JOB(JOB_NAME =>'JOB_NAME');

END;

In our case, if we want to drop the job then we can drop it like:-

BEGIN

DBMS_SCHEDULER.DROP_JOB(JOB_NAME =>'DAILY_TESTING_JOB');

END;

Read Also:-  Creating Triggers