Oracle Job Scheduler

Oracle Job Schedular

The DBMS_JOB package is actually an API into an Oracle subsystem known as the job queue . The Oracle job queue allows for
the scheduling and execution of PL/SQL routines (jobs) at predefined times and/or repeated job execution at regular intervals.
The DBMS_JOB package provides programs for submitting and executing jobs, changing job execution parameters, and removing or
temporarily suspending job execution. This package is the only interface with the Oracle job queue.

DBMS_JOB is used to schedule many different types of tasks that can be performed in PL/SQL and that require regular execution.
The job queue is used extensively by Oracle replication facilities, and was originally developed for the purpose of refreshing
Oracle snapshots. DBMS_JOB is often used by DBAs to schedule regular maintenance activities on databases, typically during
periods of low usage by end users. It can similarly be used by applications to schedule large batch operations during off
hours. The job queue can also be used to start up service programs that listen on database pipes and respond to service requests
by user sessions.

Example:

CREATE TABLE employee_info(
emp_name varchar2(20),
emp_dob date
);

CREATE TABLE message(
emp_name varchar2(20),
msg varchar2(50)
);

CREATE OR REPLACE PROCEDURE send_msg
CURSOR emp_cur IS select emp_name from employee_info ;
var date;
BEGIN
for variable in emp_cur
LOOP
select emp_dob into var from employee_info where empo_name=variable.name;
IF TO_CHAR(var,'dd-mm-yyyy')=TO_CHAR(emp_dob,'dd-mm-yyyy') THEN
INSERT INTO message VALUES(variable.emp_name,'Happy Birthday');
END IF;
END LOOP;

\\here job schedular example which call send_msg procedure to check birthday

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_full_job_definition',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN send_msg; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
/