Problem with interval using DBMS_JOB

From: plums <nc1967_at_hotmail.com>
Date: Thu, 30 Nov 2000 16:08:40 -0000
Message-ID: <905u7v$qu4$1@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 Thu Nov 30 2000 - 10:08:40 CST