How To Schedule A Job In Oracle

DBMS_JOB package is used to manage and schedule job in Oracle. Given below is a simple example with detailed steps which illustrates DBMS_JOB.

The requirement is to insert employee details into the table EMPLOYEES. When the job is invoked the details would be inserted. The details needs to passed as parameters while SUBMITting the job.

01]How to create the procedure which inserts a value to the employees table.

CREATE OR REPLACE PROCEDURE my_procedure
(arg_f_name employees.f_name%type,
arg_l_name employees.l_name%type,
arg_sex employees.sex%type,
arg_email_id employees.email_id%type,
arg_dept_name employees.dept_name%type)
AS
num number;
BEGIN
SELECT employee_seq.NEXTVAL
INTO num
FROM DUAL;

INSERT INTO employees
VALUES (
num,
arg_f_name,
arg_l_name,
arg_sex,
arg_email_id,
arg_dept_name);

END;
/

[02] How to Sumbit a job to job queue.

This job is scheduled to run everyday.

declare
l_jobnum number;
begin
dbms_job.submit(l_jobnum,
‘my_procedure(”AMITA”,”KUMAR”,”F”,”amita@abc.com”,”HR”);’,
sysdate,’sysdate+1′);
end;
/

Syntax:
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,
instance  IN  BINARY_INTEGER DEFAULT any_instance,
force     IN  BOOLEAN DEFAULT FALSE);

According to our requirement, the field WHAT can be modified.

[03]How to find the job number from dba_jobs.

select JOB from dba_jobs;

[04]How to Execute the job

exec dbms_job.run(2);

[05]How to Remove a job

exec dbms_job.remove(2);

[06]How to modify a job

exec dbms_job.change(2, null, sysdate+3, null);

[07]How to Stop a scheduled job.

exec dbms_job.broken(2,TRUE);

Article by Divya