Home » SQL & PL/SQL » SQL & PL/SQL » dbms_job.submit is not taking variable in INTERVAL column (Oracle 9i)
dbms_job.submit is not taking variable in INTERVAL column [message #393542] Mon, 23 March 2009 05:29 Go to next message
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 #393546 is a reply to message #393542] Mon, 23 March 2009 05:34 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
That'd be because dbms_job doesn't have a clue what P41 and P42 are.
Try calculating the value to you want to add to sysdate for the interval before calling dbms_job.
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 Go to previous message
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
Previous Topic: Effective delete Syntax
Next Topic: drop bitmap indexes
Goto Forum:
  


Current Time: Sun Dec 04 14:55:35 CST 2016

Total time taken to generate the page: 0.07037 seconds