RE: DBMS

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Wed, 2 Mar 2005 23:32:03 +0000 (GMT)
Message-ID: <20050302233204.59117.qmail@web86906.mail.ukl.yahoo.com>

I've always been slow at putting together complex interval functions...so some other solutions I typically use are:

use the job definition

dbms_job.submit(:J, 'if to_char(sysdate,''hh24'') in (''12'',''00'') then

                               my_proc;
                         end if;'

b) put the same code in the proc itself, with an override capacity as well, eg

        procedure my_proc(p_force boolean default false) is
        begin
           if to_char(sysdate,'HH24')) in ('12','00') or p_force then
              <code>
           end if;
        end;

c) control the interval explicitly from within the procedure, eg

        procedure my_proc(p_next_date in out date) is
        begin
          <code>
          p_next_date := ...
        end;

and you submit with: dbms_job.submit(:j, 'my_proc(next_date);'

On a similar vein, we recently enacted a standard where all our submittable jobs should be self-contained, that is, they define internally their own submission facility, so procedures would look like:

procedyre my_proc(p_action varchar2 default 'RUN') is begin

      if p_action = 'RUN' then
         <normal code>
      elsif p_action = 'STOP' then
         select job into j from dba_jobs where what = 'my_proc';
         dbms_job.remove(j); 
      elsif p_action = 'START' then
         dbms_job.submit('my_proc;', ... );
      end if;

end;

This way your jobs don't need additional scripts/documentation etc etc for how and when they are meant to be run.

hth
connor

"Goulet, Dick" <DGoulet_at_vicr.com> wrote:

> Steve,
>
> Have similar, but not identical problems. We want to skip over
> weekends. The interval parameter only needs to evaluate to a date data
> type, therefore the solution here has been to use the decode function.
> It works well, although how you'd use it, I'm not 100% sure. Need to
> look in a book & experiment a touch.
>
> -----Original Message-----
> From: Dirschel, Steve [mailto:Steve.Dirschel_at_bestbuy.com]=20
> Sent: Wednesday, March 02, 2005 4:06 PM
> To: oracle-l_at_freelists.org
> Subject: DBMS_JOB
>
> Oracle 9.2
>
> We want to schedule a job via DBMS_JOB to run at 12:30am and 12:30pm.
> I'd prefer to schedule 1 job to do this rather than 1 job to run at
> 12:30am and 1 job at 12:30pm (I don't want them to run on top of each
> other).
>
> When I schedule the job via this command:
>
> execute dbms_job.submit(job=3D3D>:jobno, what=3D3D>'program_name;',
> next_date=3D3D>trunc(sysdate+1)+1/48,
> interval=3D3D>'trunc(SYSDATE+12/24,''HH'')');
>
> It works as expected. But the problem I have is if the job gets broken
> or the db is down the next time it executes it will schedule itself 12
> hours in the future- if it happens to run at 2:10am then the next run
> would be 2:00pm. Is there a way with 1 job to force it to only run at
> 12:30am and 12:30pm regardless of the time of current execution?
>
> Thanks
>
> --
> http://www.freelists.org/webpage/oracle-l
> --
> http://www.freelists.org/webpage/oracle-l
>

Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 02 2005 - 18:35:16 CST

RE: DBMS_JOB