Re: dbms_job

"Bernard" <bernard.mathysse_at_kinetech.net> wrote in message news: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.

Bernard,

Look again at your trunc. Close parenthesis is in the wrong place. You need to trunc sysdate *first*, then add the hours.

But there are more elegant ways than breaking and unbreaking. I suggest you consider judicious use of the decode function in your interval expression.

Alternatively, you might prefer to have the package itself check the day and time, and simply do nothing outside core hours.

If you wanted to go the second way, you could even put in a little table to hold the start/stop days and times, and have the package read that to determine whether it is supposed to do something or not.

The possibilities are myriad. Personally, I don't particularly like setting up jobs which change other jobs. It just seems a bit too Heath Robinson to me, but of course YMMV. But if you do go that way, remember to commit!

Hope this helps,
Paul Received on Thu Dec 05 2002 - 14:20:58 CST