Scheduled JOBS in Oracle
–1. db job test table- will be used to store the values by the job
CREATE TABLE test_jobs
(
test_jobs_id NUMBER(20, 0) NOT NULL
, run_time timestamp(6)
, CONSTRAINT test_jobs_pk PRIMARY KEY
(
test_jobs_id
)
ENABLE
);
–DESC test_jobs;
2. Create a sequence for the test table
CREATE SEQUENCE TEST_JOBS_SEQ INCREMENT BY 1;
–select TEST_JOBS_SEQ.NEXTVAL from DUAL;
–3.Create the job in db
DECLARE
db_job NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => db_job
,what => ‘begin db_jobs.run_test_jobs;commit; end;’
,next_date => TO_DATE(’09-11-2011 04:00:00′,’dd/mm/yyyy hh24:mi:ss’)
,INTERVAL => ‘SYSDATE + 30/86400′
,no_parse => TRUE
);
END;
/
/*
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′
Do not re-execute NULL
*/
–4.Get the JOB number from the following query
.Select * from test_jobs order by 1 desc;
–created job numer 1103
SELECT * FROM user_jobs where JOB IN (1103);
SELECT * FROM user_jobs;
SELECT JOB,LAST_DATE, LAST_SEC, NEXT_DATE, NEXT_SEC, INTERVAL, WHAT FROM user_jobs where JOB IN (1103);
–5. Create a package for the test
– db jobs pkg
create or replace
package db_jobs as
PROCEDURE run_test_jobs;
end db_jobs;
– db job package body
create or replace
package body db_jobs as
PROCEDURE run_test_jobs IS
sql_stmt varchar2(1000);
BEGIN
DBMS_OUTPUT.PUT_LINE (‘=============> DB JOB STARTED BY .. ‘ || sysdate);
sql_stmt := ‘INSERT INTO test_jobs(TEST_JOBS_ID, RUN_TIME) VALUES (:1, :2)’;
EXECUTE IMMEDIATE sql_stmt USING TEST_JOBS_SEQ.NEXTVAL, SYSDATE;
DBMS_OUTPUT.PUT_LINE (‘RECORD INSERETD INTO P_QUAL_REPORT_TIBCO_DATA TABLE ‘ || sql_stmt );
COMMIT;
DBMS_OUTPUT.PUT_LINE (‘=============> DB JOB COMPLETED BY .. ‘ || sysdate);
END;
end db_jobs;
–6.Remove the db jobs using number
BEGIN
DBMS_JOB.REMOVE(1103); –PASS JOB NAME
COMMIT;
END;
/
