Re: dbms_job -> Heath Robinson

For those poor unfortunates unfamiliar with Paul's reference to Heath Robinson, here is a link that showcases the artist's work. You will probably understand the reference soon enough...

http://www.btinternet.com/~a.ghinn/heath.htm

Martin Doherty

Paul Brewer wrote:

>"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 - 18:48:07 CST