Re: Problem with interval using DBMS_JOB

Try without interval, and execute your procedure with a parameter date and test if it is saturday sum 2 days for monday.

"plums" <nc1967_at_hotmail.com> wrote in message news:905u7v$qu4$1_at_pheidippides.axion.bt.co.uk...
> Hi,
>
> I am trying to submit a job with a reasonably complex interval date
> I want the interval to be every Monday, Tuesday, Wednesday, Thursday and
> Friday at 04.00am
>
> I am submitting the job as follows
>
> var jobno number
> begin
> dbms_job.submit(
> job => :jobno,
> what => 'begin package_to_execute.proc_to_execute; end;',
> next_date => trunc(sysdate + 1) + 4/24,
> interval => 'TRUNC(LEAST(NEXT_DAY(SYSDATE,"MONDAY"),
> NEXT_DAY(SYSDATE,"TUESDAY"),NEXT_DAY(SYSDATE,"WEDNESDAY"),
> NEXT_DAY(SYSDATE,"THURSDAY"),NEXT_DAY(SYSDATE,"FRIDAY")))+4/24'
> );
> end;
> /
>
> With this I get the error
>
> ERROR at line 6:
> ORA-06550: line 6, column 47:
> PLS-00103: Encountered the symbol "MONDAY" when expecting one of the
> following:
> . ( ) , * @ % & = - + < / > in mod not rem an exponent (**)
> <> or != or ~= >= <= <> and or like between is null is not ||
>
> If I then change the ' to " around the days I get the error
>
> ERROR at line 1:
> ORA-23319: parameter value
>

 "TRUNC(LEAST(NEXT_DAY(SYSDATE,"MONDAY"),NEXT_DAY(SYSDATE,"TUESDAY"),NEXT_DAY
>

 (SYSDATE,"WEDNESDAY"),NEXT_DAY(SYSDATE,"THURSDAY"),NEXT_DAY(SYSDATE,"FRIDAY"
> )))+4/24
> " is not appropriate
> ORA-06512: at "SYS.DBMS_JOB", line 49
> ORA-06512: at "SYS.DBMS_JOB", line 119
> ORA-06512: at line 2
>
> I got the interval date expression from the Oracle professional
 newsletter.
>
> Is the expression ok?
> Or is there an alternative way to get the interval I require ?
>
> Many thanks
>
> Plums
>
>
>
>
Received on Mon Dec 04 2000 - 18:11:48 CST