Re: dbms_job

I am using functions like that in dbms_jobs, and they work fine (after a little debugging!)
What was more difficult was preventing a job from running at a bad time, i.e. a batch job heavy on resources should not run at peak login time even if it failed to run earlier! This involved create a "shell" to run the procedures, with logic to determine if it should run, email an admin, or do something else.

Steve Brown
DBA, Information Technology International

"Anton Buijs" <aammbuijs_at_xs4all.nl> wrote in message news:3df3b0fe$0$11742$e4fe514c_at_news.xs4all.nl...
> The interval parameter for the dbms_job.submit procedure can be a self
> written function too as long as its call returns a valid future date. Have
> not used it like that but the docs say it must be possible.
> When you know how to write PL/SQL you can do anything you want in that
> function. Maybe you want to skip not only the weekends but official
holidays
> too?
>
>
> Bernard <bernard.mathysse_at_kinetech.net> schreef in berichtnieuws
> b7b69d6c.0212050713.79e9a694_at_posting.google.com...
> | I'm trying to use dbms_job rather than cron to run an oracle package
> | between certain times
> | of the day. To start in the morning , run several times thoughout the
> | day and then stop in the evening until the next day.
> |
> | I've tried setting the job to broken in order to stop the job and
> | unbroken in order to start it again , as so :
> |
> | First set up the job to run every 10 minutes during week days:
> |
> |
> | variable v_jobnum number;
> |
> | begin
> | DBMS_JOB.SUBMIT (:v_jobnum,'run_pkg.program;', sysdate,
> | 'TRUNC(LEAST(NEXT_DAY(SYSDATE,''MONDAY''),
> | NEXT_DAY(SYSDATE,''TUESDAY''),
> | NEXT_DAY(SYSDATE,''WEDNESDAY''),
> | NEXT_DAY(SYSDATE,''THURSDAY''),NEXT_DAY(SYSDATE,''FRIDAY'') )) +
> | 10/1440');
> | commit;
> | end;
> | /
> |
> | Then set the status to BROKEN at 6pm to stop it:
> |
> | BEGIN
> | FOR job_rec IN (SELECT job FROM user_jobs where what like
> | 'run_pkg%')
> | LOOP
> | DBMS_JOB.BROKEN(job_rec.job,TRUE,trunc(sysdate+18/24));
> | END LOOP;
> | END;
> | /
> |
> |
> | To start the job again at 8am, unset the broken flag :
> |
> | begin
> | FOR job_rec IN (SELECT job FROM user_jobs
> | where what like 'run_pkg%'
> | and broken = 'Y')
> | loop
> | dbms_job.broken(job_rec.job,FALSE,trunc(sysdate+8/24));
> | end loop;
> | end;
> | /
> |
> | But when I set the job to broken , it ignores my instruction to stop
> | at 6pm, and resets it to 01/01/00 midnight !
> |
> |
> | JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_DATE B
> | NEXT_SEC
> | --------- --------- -------- --------- -------- --------- --------- -
> | --------
> | 62 05-DEC-02 14:57:33 01-JAN-00 01-JAN-00 Y
> | 00:00:00
> |
> |
> | WhenI Try to re-run it , again it ignores any time I specified.
> |
> | JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_DATE B
> | NEXT_SEC
> | ---------- --------- -------- --------- -------- --------- --------- -
> | --------
> | 62 05-DEC-02 14:57:33 06-DEC-02 06-DEC-02 N
> | 00:00:00
> |
> |
> | Please has anyone got any idea why this doesn't seem to work ?
> |
> | The syntax for it is :
> |
> | PROCEDURE DBMS_JOB.BROKEN
> | (job IN BINARY_INTEGER
> | ,broken IN BOOLEAN
> | ,next_date IN DATE DEFAULT SYSDATE);
> |
> | So it should work.
>
>
Received on Tue Dec 10 2002 - 22:40:35 CST