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.
iqbaljAsked:
Who is Participating?
 
meowshConnect With a Mentor Commented:
The dbms_output.submit has four clauses (see below)

DBMS_OUTPUT.SUBMIT (jobno_parameter  OUT number,
                    jobprocedure     IN varchar2
                    job_first_run    IN DATE (default is sysdate)
                    job_interval     IN VARCHAR2 (default is null I think)


Hence to set one up for 5 am everyday make the third clause todays date (including time) and set the interval to 1.

I.e use

dbms_job.submit
(
<jobno_return_variable>,
<procedure_name> ,
'14-Jul-99 05:00:00','dd-mon-yy hh24:mi:ss ,
1);

and there you go


Hope this is the sort of info you are looking for


Meowsh
0
 
ddaCommented:
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;
/

0
 
iqbaljAuthor Commented:
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.

0
All Courses

From novice to tech pro — start learning today.