garden3.jpg
DBMS_JOB

Oracle database dbms jobs

Quick overview:

1) Make sure you have the parameter job_queue_processes set to a value greater than 0.

2) Make sure you have A commit after the call to dbms_job.submit. dbms_job.submit does not commit, dbms_job.submit will not ever run until after you type COMMIT.

3) If the system goes down before a job is run, it would be run as soon as the system is up again.

The job will execute shortly after startup. The job queues look for all jobs that SHOULD HAVE BEEN run and runs them.

The submit procedure submits a new job to the job queue. The following is

the header for the procedure:

PROCEDURE DBMS_JOB.SUBMIT

(job OUT BINARY_INTEGER,

what IN VARCHAR2

NEXT_DATE IN DATE DEFAULT SYSDATE,

Interval IN VARCHAR2 DEFAULT ‘null’,

No_parse IN BOOLEAN DEFAULT FALSE);

The parameters are summarized as follows:

Parameter Description

Job Unique identifier of the job

What PL/SQL code to execute as a job

Next_date Next execution date of the job

Interval Date expression to compute next execute of date

No_parse Flag indicating whether to parse job PL/SQL at time

of submission (FALSE) or execution (TRUE)

The following example shows how to schedule a job to execute Monday through

Friday:

DBMS_JOB.SUBMIT (:v_jobnum,’my_procedure;’, sysdate,

‘TRUNC(LEAST(NEXT_DAY(SYSDATE,”MONDAY”),

NEXT_DAY(SYSDATE,”TUESDAY”),

NEXT_DAY(SYSDATE,”WEDNESDAY”),

NEXT_DAY(SYSDATE,”THURSDAY”),

NEXT_DAY(SYSDATE,”FRIDAY”) ))’);

commit;

The following example shows how to schedule a job to execute Monday through

Friday at 6:00 pm:

DBMS_JOB.SUBMIT (:v_jobnum,’my_procedure;’, sysdate,

‘TRUNC(LEAST(NEXT_DAY(SYSDATE,”MONDAY”),

NEXT_DAY(SYSDATE,”TUESDAY”),

NEXT_DAY(SYSDATE,”WEDNESDAY”),

NEXT_DAY(SYSDATE,”THURSDAY”),

NEXT_DAY(SYSDATE,”FRIDAY”) )) + 18/24′);

commit;

Midnight Example:

The following example shows how to schedule a job to execute at midnight

every night:

DBMS_JOB.SUBMIT (:v_jobnum, ‘my_procedure;’, trunc(sysdate) + 1,

’sysdate + 1′);

The ‘trunc(sysdate) + 1′ sets the time back to the current day midnight,

and informs the scheduler to start the job at the following midnight.

The ’sysdate + 1′ sets the interval to 1 day. This job would run the first

time at midnight, then every midnight afterwards.

To schedule the job at 8 a.m., specify the next_date parameter as

trunc(sysdate) + 1 + 8/24.

Interval samples:

sysdate+1/24 –> Every hour.

‘NEXT_DAY(TRUNC(SYSDATE),”FRIDAY”)+12 –> Every Friday at Noon.

The value returned by trunc(sysdate) is midnight today and the value returned by

trunc(sysdate + 1) is midnight tomorrow.

To remove a job from the queue:

execute dbms_job.remove(970);

Where 970 is the job ID. You have to be logged on as the owner of the job.

Actual testing:

Sample 1):

SQL> create table test_dbms_jobs (when date, val1 number);

Table created.

variable v_jobnum number

begin

dbms_job.submit(:v_jobnum, ‘insert into test_dbms_jobs values(sysdate,1);’,trunc(sysdate) +1 + 9/24);

commit;

end;

/

PL/SQL procedure successfully completed.

–> Here the insert will be scheduled for next day at 9 AM. With the interval not set therefore

the interval defaults to NULL and the job will be executed only once at 9AM the next day and then will be removed from the job queue.

Sample 2):

SQL> variable v_jobnum number

SQL> begin

2 dbms_job.submit(:v_jobnum, ‘insert into test_dbms_jobs values(sysdate,1);’,trunc(sysdate)+ 9/24);

3 end;

4 commit;

5 /

PL/SQL procedure successfully completed.

–> Here the insert will be scheduled on the same day at 10 AM. With the interval not set

therefore the interval defaults to NULL and the job will be executed only once at 10 AM the same day and then will be removed from the job queue.

Sample 3):

variable v_jobnum number

begin

dbms_job.submit(:v_jobnum, ‘insert into test_dbms_jobs values(sysdate,1);’, sysdate, ’sysdate + (1/(24*60*60))’);

commit;

end;

/

PL/SQL procedure successfully completed.

–> Here the insert will be scheduled on the same and will run indefinitely every second.

Sample 4):

variable v_jobnum number

begin

dbms_job.submit(:v_jobnum, ‘insert into test_dbms_jobs values(sysdate,1);’, trunc(sysdate) + 15/24);

commit;

end;

/

–> Here the insert will be scheduled on the same day at 3 PM. With the interval not set

therefore the interval defaults to NULL and the job will be executed only once at 3 PM the same day and then will be removed from the job queue.

Sample 5):

variable v_jobnum number

begin

dbms_job.submit(:v_jobnum, ‘insert into test_dbms_jobs values(sysdate,1);’, sysdate, ’sysdate + (1/(24*60))’);

commit;

end;

/

PL/SQL procedure successfully completed.

–> Here the insert will be scheduled on the same and will run indefinitely every minute.

Sample 6):

Calling dbms_job.submit from within a stored procedure:

CREATE OR REPLACE procedure TEST_SCHEDULE_JOB as

BEGIN

INSERT INTO TableA

SELECT * FROM TableB

COMMIT;

–RC := 0;

END;

/

–> Here we create a test proc to insert into a test table.

create or replace procedure test_schedule(RC out varchar2) as

v_jobnum number;

begin

delete from TableA;

commit;

dbms_job.submit(v_jobnum, ‘TEST_SCHEDULE_JOB;’ ,trunc(sysdate) + 15/24);

commit;

end;

/

–> Here we created another proc from within which we call dbms_job.submit to run at 3 PM on the same day.

SQL> variable rc varchar2

begin

test_schedule(:RC);

end;

/

–> Now we call the stored proc which executes and schedules the job.

Sample 7):

How to schedule at 3:11 PM:

dbms_job.submit(v_jobnum, ‘TEST_SCHEDULE_JOB;’ ,trunc(sysdate) + 15.11/24);

commit;

Sample 8):

variable v_jobnum number

begin

dbms_job.submit(:v_jobnum, ‘execute immdiate “drop user bobby cascade”;’);

commit;

end;

/

Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.

Latest Blog Items

email

Thank you for your interest in idbasolutions.

Contact us using this email: contact.us@idbasolutions.com