Re: dbms_job and setting the interval

In article <f3159b28.0110151501.795ccb23_at_posting.google.com>, sgelberg_at_optonline.net says...
>
>Can you specify a variable to use as the interval value in the
>dbms_job.submit:
>
>for ex:
>
> v_matchInterval CONSTANT NUMBER := 10/86400; -- 10 Seconds
>.
>.
>.
> DBMS_JOB.SUBMIT(jobno, 'matching.matchfacade;', sysdate, sysdate +
>v_matchInterval);

the interval is a STRING which will be selected from DUAL at run time right before we run your job.

So, only if:

SQL> select sysdate+v_matchinterval from dual;

works in sqlplus would it work in dbms_job as an interval. It won't work by the way.

What you could do is:

create function f_matchInterval return number as
begin

   return 10/24/60/60;
end;
/

and now:

DBMS_JOB.SUBMIT(jobno,

               'matching.matchfacade;', 
                sysdate, 
               'sysdate + f_matchInterval' 
               );

will work.

Easier still (maybe) is to code matching.matchfacade to take an IN OUT parameter of a DATE type

...
procedure matchfacade( p_next_date in out date ) is

   ....
begin

   p_next_date := sysdate + 10/24/60/60;    .......

end;

And then schedule:

DBMS_JOB.SUBMIT(jobno,

               'matching.matchfacade;', 
                sysdate );

That way the job itself can programatically decide when to next run without having the "helper" function. (if you are interested in more background on this technique, how it actually works and all -- my book has a chapter on using dbms_job and all of the options)...

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp