OraFAQ Forum: SQL & PL/SQL » Fixing Jobs with Time

Home » SQL & PL/SQL » SQL & PL/SQL » Fixing Jobs with Time (9i - 9.2.0.8 - Server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Fixing Jobs with Time [message #420023]

Wed, 26 August 2009 10:38

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;

Report message to a moderator

Re: Fixing Jobs with Time [message #420024 is a reply to message #420023]

Wed, 26 August 2009 11:16

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use dbms_job.change.

Regards
Michel

Report message to a moderator

Re: Fixing Jobs with Time [message #420026 is a reply to message #420024]

Wed, 26 August 2009 11:26

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.

Report message to a moderator

Re: Fixing Jobs with Time [message #420029 is a reply to message #420026]

Wed, 26 August 2009 11:40

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Fixing Jobs with Time [message #420032 is a reply to message #420029]

Wed, 26 August 2009 11:52

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.

Report message to a moderator

Re: Fixing Jobs with Time [message #420036 is a reply to message #420032]

Wed, 26 August 2009 12:05

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Fixing Jobs with Time [message #420041 is a reply to message #420036]

Wed, 26 August 2009 12:46

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;

Report message to a moderator

Re: Fixing Jobs with Time [message #420043 is a reply to message #420023]

Wed, 26 August 2009 12:55

BlackSwan
Messages: 26766
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.

Report message to a moderator

Previous Topic:	Sql Problem (merged 4)
Next Topic:	Using online option during index rebuild.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 11:07:51 CST 2025