Re: DBMS_JOB schedule

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 11 Feb 2005 04:17:09 -0800
Message-ID: <1108124229.166207.304980@o13g2000cwo.googlegroups.com>

suresh manampilly via OracleMonster.com wrote:
> Hi,
>
> I tired it but I get following message:
>
> SQL> exec

dbms_job.isubmit(65,'jncdm_mexx_purfact2;',sysdate,'decode(to_char(sysdate,'DAY'),'FRIDAY'
> ,'trunc(sysdate + 1) + 15/24 + 00/(24 * 60)','trunc(sysdate + 1) +
13/24 + 40/(24 * 60)')');
> BEGIN

dbms_job.isubmit(65,'jncdm_mexx_purfact2;',sysdate,'decode(to_char(sysdate,'DAY'),'FRIDAY','tr
>
>

            *
> ERROR at line 1:
> ORA-06550: line 1, column 83:
> PLS-00103: Encountered the symbol "DAY" when expecting one of the
following:
> . ( ) , * @ % & | = - + < / > at in mod not range rem => ..
> <an exponent (**)> <> or != or ~= >= <= <> and or like
> between is null is not || is dangling
>
>
> SQL>
>
> Is there anything wrong with my syntax?
>

'DAY' and 'FRIDAY' are strings within a string so must be properly delimited as,

set serverout on
declare

   job_ integer;
begin
  dbms_job.submit(job => job_,

                  what => 'your_proc;',
                  next_date => sysdate,
                  interval => 'decode(to_char(sysdate,

''DAY''),''FRIDAY'',trunc(sysdate + 1) + 15/24,trunc(sysdate + 1) + 13/24)');
  dbms_output.put_line('Job created:'||job_);   commit;
end;
/

Regards
/Rauf Received on Fri Feb 11 2005 - 06:17:09 CST