dbms_job

From: Bernard <bernard.mathysse_at_kinetech.net>
Date: 5 Dec 2002 07:13:28 -0800
Message-ID: <b7b69d6c.0212050713.79e9a694@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 Thu Dec 05 2002 - 09:13:28 CST