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;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s