| dbms_job.submit is not taking variable in INTERVAL column [message #393542] |
Mon, 23 March 2009 05:29  |
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
|
Senior Member |
|
|
Hi All,
WORKING CODE
============
dbms_job.submit(job_num,
'CIMSR2.CODER_REPORT_ON_EXCEL_SHEET.MAIN(''' || directory_in || ''');',
TRUNC(sysdate)+to_number(P41)/24 + to_number(P42)/(24*60),
'sysdate+1';
======================================
NOT WORKING
-----------
dbms_job.submi(job_num,
'CIMSR2.CODER_REPORT_ON_EXCEL_SHEET.MAIN(''' || directory_in || ''');',
TRUNC(sysdate)+to_number(P41)/24 + to_number(P42)/(24*60),
'TRUNC(sysdate+1)+to_number(P41)/24 + to_number(P42)/(24*60)');
In normal condition the next date the Oracle is storing in DBA_JOBS are time which I have given from front end + the executing time of the code_report_on_excel_sheet (that varies from 5 sec to more) BUT i want to schedule the job exactly the same time each day. The above is working if the P41 and P42 is not taking in the INTERVAL section and only if I give 'sysdate+1'.
in that case it will add some few seconds for the next scheduled job.
Ashu
|
|
|
|
|
|
| Re: dbms_job.submit is not taking variable in INTERVAL column [message #393563 is a reply to message #393546] |
Mon, 23 March 2009 06:53  |
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
|
Senior Member |
|
|
thank you cookie... it is working fine as per the desired functionality.
I have given the following syntax:
dbms_job.submit(job_num, 'CIMSR2.CODER_REPORT_ON_EXCEL_SHEET.MAIN(''' || directory_in || ''');',TRUNC(sysdate)+to_number(P41)/24 + to_number(P42)/(24*60),'TRUNC(sysdate+1)+to_number('||P41||')/24 + to_number('||P42||')/(24*60)');
Ashu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
