Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_JOB Problem
DBMS_JOB Problem [message #236148] Wed, 09 May 2007 03:25 Go to next message
longkerdandy
Messages: 2
Registered: May 2007
Junior Member
Hi
I tried to use DBMS_JOB for a job schedule on a Oracle 9i DB.
I wrote something like this:
  -- Function and procedure implementations
  procedure scheduler
   -- <LocalVariable> <Datatype>;
  IS
     jobno   NUMBER;
  BEGIN
     DBMS_JOB.SUBMIT
        (job  => jobno
        ,what => 'REPORT.LAST_MONTH_REPORT();'
        ,next_date => SYSDATE
        ,interval  => 'last_day(trunc(sysdate)) + 1 + 3/24');
     COMMIT;
  END;

And after calling this procedure, i was able to see it at the table "user_jobs",(also at "dba_jobs" and "all_jobs"). The 'NEXT_DATE' seems just right.
But the job didn't run when the time comes, and the table "dba_jobs_running" was empty.

Plus, if i use "DBMS_JOB.RUN" to force it runing, it works just fine. I will see it at "dba_jobs_running", and it will finish its job and update the "user_jobs" table.

The "JOB_QUEUE_PROCESSES" is 10 at my DB.
Any clue to this?

LongkerDandy
Re: DBMS_JOB Problem [message #236153 is a reply to message #236148] Wed, 09 May 2007 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Retry and watch what is in user_jobs.last_date.
You have to wait a little while before the job is executed.

Regards
Michel
Re: DBMS_JOB Problem [message #236159 is a reply to message #236153] Wed, 09 May 2007 03:52 Go to previous messageGo to next message
longkerdandy
Messages: 2
Registered: May 2007
Junior Member
I'm sure it didn't run.
This problem bother me for several days.

The "LAST_DATE" is null, unless i force it to run by DBMS_JOB.RUN

Regards
LongkerDandy
Re: DBMS_JOB Problem [message #236160 is a reply to message #236159] Wed, 09 May 2007 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Any message in alert.log?
Double check your job_queue_processes value.

Regards
Michel
Re: DBMS_JOB Problem [message #236184 is a reply to message #236160] Wed, 09 May 2007 04:31 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Add a few seconds to your next_date
Previous Topic: Calling a sql script from a shell script
Next Topic: Inserting null or space in DATE datatype
Goto Forum:
  


Current Time: Mon Dec 05 06:40:44 CST 2016

Total time taken to generate the page: 0.06053 seconds