Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_JOB.SUBMIT multiple next_date / intervals (Oracle 10g 10.2.0.1.0)
DBMS_JOB.SUBMIT multiple next_date / intervals [message #408572] Tue, 16 June 2009 21:42 Go to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Hi experts,

This seems like a trivial question but i can't find any links or documentations regarding my problem. Everytime all i can find are the infos...

DBMS_JOB.SUBMIT (
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT sysdate,
interval IN VARCHAR2 DEFAULT 'null',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT any_instance,
force IN BOOLEAN DEFAULT FALSE);


I scheduled jobs that run every minute, hour etc like with this..

interval  => 'sysdate+(1/(24*60))'


BUT i can't seem to think of a way of scheduling jobs with multiple intervals. I need to run my procedure every 6am, 12pm and 6pm of every day..

declare
  my_job number;
begin
  dbms_job.submit(my_job, 
    'MY_PROC1;',
    (TRUNC(SYSDATE)+(6/24),TRUNC(SYSDATE)+(12/24),TRUNC(SYSDATE)+(18/24));
  Commit;
end;
/


ORA-06550: line 6, column 74:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

   ) , * & | = - + < / > at in is mod remainder not rem => ..
   <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
   LIKE4_ LIKEC_ between overlaps || multiset year DAY_ member
   SUBMULTISET_
The symbol ")" was substituted for ";" to continue.


declare
  my_job number;
begin
  dbms_job.submit(my_job, 
    'PLP_CHECK_POST_PAYMENT;',
     to_date ('06-17-2009 11:00:00','MM-DD-YYYY HH24:MI:SS'),
	('TRUNC(SYSDATE)+(6/24))','TRUNC(SYSDATE)+(12/24)','TRUNC(SYSDATE)+(18/24)');
  Commit;
end;
/

ORA-06550: line 7, column 78:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

   ) , * & | = - + < / > at in is mod remainder not rem => ..
   <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
   LIKE4_ LIKEC_ between overlaps || multiset year DAY_ member
   SUBMULTISET_
The symbol ")" was substituted for ";" to continue.


Please help... Any links or info will be much appreciated.. Thanks in advance..

Regards,
Wilbert
Re: DBMS_JOB.SUBMIT multiple next_date / intervals [message #408573 is a reply to message #408572] Tue, 16 June 2009 22:16 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
DBMS_JOB does not handle multiple intervals.
1) 1 job that runs every 6 hours & immediately exits at midnight
2) 2 jobs, 1 run every 12 hours at 6AM & 6PM, and 1 runs at 12PM
Re: DBMS_JOB.SUBMIT multiple next_date / intervals [message #408575 is a reply to message #408572] Tue, 16 June 2009 23:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use a function to compute the interval like the following:
http://www.orafaq.com/forum/m/313430/102589/?srch=NextJobDate#msg_313430
and you put the function name in the "interval" parameter.

Regards
Michel

[Updated on: Tue, 16 June 2009 23:19]

Report message to a moderator

Re: DBMS_JOB.SUBMIT multiple next_date / intervals [message #408585 is a reply to message #408572] Wed, 17 June 2009 00:17 Go to previous message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Thanks for the time, valuable suggestions and informative link BlackSwan and Michel Cadot...

Bless you and good day! Smile

Regards,
Wilbert
Previous Topic: Ref cursor and parsing the string question
Next Topic: Please help me
Goto Forum:
  


Current Time: Fri Dec 02 19:06:09 CST 2016

Total time taken to generate the page: 0.12665 seconds