I am delivering a script that will be run by a customer. At the beginning of the script, I break the jobs so that they will not start during the script execution. At the end of the script, I am fixing the jobs. The only problem that I am having is that a job that is fixed executes immediately unless you set the next_date. I want to populate the value of next_date with the current value of INTERVAL from user_jobs. Is there any way to do that?
This is the closest I can come but it fails at compilation:
Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook
This is the closest I can come but it fails at compilation:
Code:
CREATE OR REPLACE PROCEDURE job_fixer
AS
CURSOR broken_jobs_cur
IS
SELECT job, interval
FROM user_jobs
WHERE broken = 'Y';
dtNext date;
strQuery VARCHAR(200);
BEGIN
FOR job_rec IN broken_jobs_cur
LOOP
strQuery := 'select '|| job_rec.interval || ' INTO dtNext from dual';
execute immediate(strquery);
DBMS_JOB.BROKEN(JOB=>job_rec.job,broken=>FALSE, NEXT_DATE=>dtNext);
END LOOP;
END job_fixer;
Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook