ritesh raj singh

• Jul 4th, 2006

 

Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM?

See for this what you have to do is create a view
which will result the time to you
like

create view job_interval as
select TRUNC(sysdate+1)+decode(to_char(sysdate+1,'DAY'),'WED',9/24,2/24) TIME From dual;

WHAT THIS VIEW WILL DO IS IT WILL RETURN YOU 2 AM FOR DAYS OTHER THAN WEDNESDAY AND 9 AM FOR WEDNESDAY.

NOW USE THIS VIEW IN DBMS_JOB.SUBMIT AND PASS (SELECT TIME FROM JOB_INTERVAL) IN YOUR INTERVAL ARGUMENT IN
DBMS_JOB.SUBMIT AS
EXEC DBMS_JOB.SUBMIT(:JOBNO,'YOUR_WORK','TRUNC(SYSDATE+2)+9/24',(SELECT TIME FROM JOB_INTERVAL));

CHECK IT OUT IT WORKS ...
FOR ANY CLARIFICATION GET BACK TO ME
riteshrajsingh1984@yahoomail.com

Ritesh Raj Singh
Database Administration Team
ICICI Ltd
Mahalakshmi Data Centre
Mumbai 400039