DBMS JOB is a job scheduler package which schedules and manages jobs in the job queue.
Use case:
Creating a job(execute hourly) which calls a package and procedure to update the polling_status field of the table from 1 to 2, then these table records become candidates for the SOA db polling and further processing. also, update the job execution from hourly to daily once.
To check the existing jobs:
Select * from user_jobs;
Create the required table, package and procedure.
Query to create table:
CREATE TABLE customers
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50),
polling_status number(2),
eligble_for_credits varchar2(10),
processing_status varchar2(50)
);
Package Specification:
create or replace PACKAGE "CUSTOMEREVENTLOG"
AS
PROCEDURE UPDATE_POLLING_STATUS;
END CUSTOMEREVENTLOG;
Package body:
create or replace PACKAGE BODY "CUSTOMEREVENTLOG"
AS
PROCEDURE UPDATE_POLLING_STATUS
AS
BEGIN
UPDATE customers
SET POLLING_STATUS = '2'
WHERE POLLING_STATUS = '1';
END UPDATE_POLLING_STATUS;
END CUSTOMEREVENTLOG;
Query to create JOB:
To submit a job to the job queue, use the following 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);
*JOB: This is the identifier assigned to the job you created. You must use this job number whenever you want to alter or remove the job.
*WHAT: This is the PL/SQL code you want to have executed. The WHAT parameter must end with a semi-colon.
*NEXT_DATE: The next date when the job will be run. The default value is SYSDATE.
*INTERVAL: The date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL. This parameter is a VARCHAR2 and must be enclosed in single quotes.
*NO_PARSE: This is a flag. If NO_PARSE is set to FALSE (the default), Oracle parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.
*Instance and Force: Use the parameters instance and force to control job and instance affinity. The default value of instance is 0 (zero) to indicate that any instance can execute the job. To run the job on a certain instance, specify the instance value. Oracle displays error ORA-23319 if the instance value is a negative number or NULL.The force parameter defaults to false. If force is TRUE, any positive integer is acceptable as the job instance. If force is FALSE, the specified instance must be running, or Oracle displays error number ORA-23428.
Created Job:
DECLARE
v_numjob NUMBER := 111;
BEGIN
DBMS_JOB.SUBMIT(v_numjob,'CUSTOMEREVENTLOG.UPDATE_POLLING_STATUS();',sysdate,'sysdate+1/24');
END;
/
 |
| Job execute Hourly |
Wait 1 hour and check the result:
DBMS job runs and it updated the polling status from 1 to 2.
Enable/disable oracle job using job id:
Disable:
begin
dbms_job.broken(101, true);
commit;
end;
/
Enable:
begin
dbms_job.broken(101, false);
commit;
end;
/
**Broken =N means start and Broken =Y means stop job
Job Intervals examples
Execute daily 'SYSDATE + 1'
Execute once per week 'SYSDATE + 7'
Execute hourly 'SYSDATE + 1/24'
Execute every 10 min. 'SYSDATE + 10/1440'
Execute every 30 sec. 'SYSDATE + 30/86400'
DBMS_JOB.CHANGE
To alter user-definable parameters associated with a job, use the following syntax:
DBMS_JOB.CHANGE( JOB IN BINARY_INTEGER,
what IN VARCHAR2 DEFAULT NULL,
next_date IN DATE DEFAULT NULL,
interval IN VARCHAR2 DEFAULT NULL,
instance IN BINARY_INTEGER DEFAULT NULL,
force IN BOOLEAN DEFAULT FALSE );
Example: To execute daily
DECLARE
BEGIN
DBMS_JOB.CHANGE(101,'CUSTOMEREVENTLOG.UPDATE_POLLING_STATUS();',sysdate,'sysdate+1');
COMMIT;
END;
/
For more details click here
Oracle-Help-Center.
You can also schedule using DBMS_SCHEDULER. Fore more details click here
DBMS_SCHEDULER.
