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 Sun Dec 08 2002 - 14:52:10 CST
