Home » SQL & PL/SQL » SQL & PL/SQL » Fixing Jobs with Time (9i - 9.2.0.8 - Server 2003)
Fixing Jobs with Time [message #420023] Wed, 26 August 2009 10:38 Go to next message
NPWwolf00
Messages: 4
Registered: August 2009
Junior Member
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:

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;
Re: Fixing Jobs with Time [message #420024 is a reply to message #420023] Wed, 26 August 2009 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use dbms_job.change.

Regards
Michel
Re: Fixing Jobs with Time [message #420026 is a reply to message #420024] Wed, 26 August 2009 11:26 Go to previous messageGo to next message
NPWwolf00
Messages: 4
Registered: August 2009
Junior Member
Thank you for the response. Are you suggesting that I use DBMS_JOB.CHANGE to alter the interval to a future date and then change it back? I don't see how else I would apply that. It doesn't seem to allow me to set BROKEN=FALSE.
Re: Fixing Jobs with Time [message #420029 is a reply to message #420026] Wed, 26 August 2009 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do it in 2 steps: one change then unbreak.
Why your current solution does not work? It compiles fine for me:
SQL> CREATE OR REPLACE PROCEDURE job_fixer
  2  AS
  3     CURSOR broken_jobs_cur
  4     IS
  5     SELECT job, interval
  6       FROM user_jobs
  7      WHERE broken = 'Y';
  8    dtNext  date; 
  9    strQuery VARCHAR(200);
 10  BEGIN
 11     FOR job_rec IN broken_jobs_cur
 12     LOOP
 13        strQuery := 'select '|| job_rec.interval || ' INTO dtNext from dual';
 14        execute immediate(strquery);
 15        
 16        DBMS_JOB.BROKEN(JOB=>job_rec.job,broken=>FALSE, NEXT_DATE=>dtNext);
 17     END LOOP;
 18  END job_fixer;
 19  /

Procedure created.


Regards
Michel
Re: Fixing Jobs with Time [message #420032 is a reply to message #420029] Wed, 26 August 2009 11:52 Go to previous messageGo to next message
NPWwolf00
Messages: 4
Registered: August 2009
Junior Member
The current procedure will compile but fail when it executes. It doesn't like the EXECUTE IMMEDIATE statement.

I can't seem to find a way to get the value of job_rec.interval into a date value where it might be something like 'SYSDATE + (1/24)' (text).

The problem is when you unbreak the job it will execute immediately unless you have a value for next_date.
Re: Fixing Jobs with Time [message #420036 is a reply to message #420032] Wed, 26 August 2009 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try:
CREATE OR REPLACE PROCEDURE job_fixer
AS
   CURSOR broken_jobs_cur
   IS
   SELECT job, interval
     FROM user_jobs
    WHERE broken = 'Y';
BEGIN
   FOR job_rec IN broken_jobs_cur
   LOOP
      execute immediate 
        'begin DBMS_JOB.BROKEN(JOB=>job_rec.job,broken=>FALSE, NEXT_DATE=>'||job_rec.interval||')';
   END LOOP;
END job_fixer;
/

Regards
Michel
Re: Fixing Jobs with Time [message #420041 is a reply to message #420036] Wed, 26 August 2009 12:46 Go to previous messageGo to next message
NPWwolf00
Messages: 4
Registered: August 2009
Junior Member
Thanks for your help!

I was able to develop a solution:

CREATE OR REPLACE PROCEDURE job_fixer
AS
   CURSOR broken_jobs_cur
   IS
   SELECT job, interval
     FROM user_jobs
    WHERE broken = 'Y';
  dtNext  date; 
BEGIN
   FOR job_rec IN broken_jobs_cur
   LOOP
      execute immediate('select ' || job_rec.interval || ' FROM DUAL') into dtNext;
      DBMS_JOB.BROKEN(JOB=>job_rec.job,broken=>FALSE, NEXT_DATE=>dtNext);
   END LOOP;
END job_fixer;
Re: Fixing Jobs with Time [message #420043 is a reply to message #420023] Wed, 26 August 2009 12:55 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>INTERVAL NOT NULL VARCHAR2(200)
>dtNext date;
Depending upon implicit data type conversion between VARCHAR2 & DATE is unwise.
Using "execute immediate" for simple variable assignment is total overkill.

dtNext := job_rec.interval; -- works as well; but TO_DATE() should be used.
Previous Topic: Sql Problem (merged 4)
Next Topic: Using online option during index rebuild.
Goto Forum:
  


Current Time: Thu Dec 08 04:14:15 CST 2016

Total time taken to generate the page: 0.08999 seconds