Link to home
Start Free TrialLog in
Avatar of iqbalj
iqbalj

asked on

DBMS_JOB interval....

*** DBMS_JOB.SUBMIT(........, SYSDATE, SYDATE+1);

sets the next execution time next day and the time is whenever I'm submitting in SYSDATE.


How do I specify it to run everyday @5:00 AM

Thanks a lot.
ASKER CERTIFIED SOLUTION
Avatar of meowsh
meowsh
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dda
dda
Flag of Russian Federation image
I'm not shure, but try this:

variable jobno number;
alter session set nls_date_language='american';
alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
begin
dbms_job.submit(:jobno,
     'dbms_ddl.analyze_object(''table'',
        ''myschema'', ''mytable'',
        ''estimate'', null, 50);',
      to_date(to_char(sysdate,'dd.mm.yyyy') || '05:00', 'dd.mm.yyyy hh24:mi'),
      'to_date(to_char(sysdate+1,''dd.mm.yyyy'') || ''05:00'', ''dd.mm.yyyy hh24:mi'')');
end;
/

Avatar of iqbalj
iqbalj

ASKER

meowsh,

Thanks for the answer, but you had some syntax error.

See the comment from 'dda'; you need 'to_date' function and
interval 'SYSDATE +1'.

Thanks again.