DBMS_JOB not running, urgent please help

[pnrdba]

Jurij/Julian,
I tried with your code, and here i'm pasting the same, please look at these:

SQL> variable jobno number
SQL> begin
2 DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',
3 sysdate,
4 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24');
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> print jobno

JOBNO
----------
172

SQL> select JOB,SCHEMA_USER,LAST_DATE,NEXT_DATE,NEXT_SEC from user_jobs;

JOB SCHEMA_USER LAST_DATE NEXT_DATE NEXT_SEC
---------- ------------------------------ --------- --------- --------
169 CMS2ADM 09-JUL-02 15:28:54
170 CMS2ADM 09-JUL-02 16:37:53
90 CMS2ADM 08-JUL-02 09-JUL-02 10:35:25
172 CMS2ADM 09-JUL-02 03:59:06

SQL> select sysdate from dual;

SYSDATE
---------
09-JUL-02

SQL> select NEXT_DATE,NEXT_SEC,THIS_DATE,THIS_SEC,INTERVAL from user_jobs
2 where job=172;

NEXT_DATE NEXT_SEC THIS_DATE THIS_SEC
--------- -------- --------- --------
INTERVAL
--------------------------------------------------------------------------------
09-JUL-02 03:59:06
NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+3/24



Here, i'm confused, NEXT_DATE,NEXT_SEC is showing as the date and time at which i created the job. Can you please tell me how to check it up when my job is going to fire again ?? as per we set in the code???

[pnrdba]

Jurij/Julian ,
Here again i'm pasting some more results after i set the job as you mentioned. Here i'm confused like, next_date is showing as 09-JUL-02, which is today. But interval is showing as correct . Can you please clarify??

SQL> select INTERVAL from user_jobs where job=172;

INTERVAL
--------------------------------------------------------------------------------
NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+3/24

SQL> select NEXT_DATE,NEXT_SEC from user_jobs where job=172;

NEXT_DATE NEXT_SEC
--------- --------
09-JUL-02 03:59:06

[jmodic]

Your job 172 has not been run jet, it is still waiting to be run for the first time. You can see this by looking at LAST_DATE column, it is NULL. So the actual NEXT_DAT this job needs to be run is actually allready in the past, ie the moment you submited it. And why it has not been run yet? Because you didn't COMMIT your job submition. Until you enter COMMIT, you can see this job in DBA_JOBS only from the session that submitted it, while the RDBMS system does not see it yet, so it can't run it.

As soon as you commit your submition, the job will run and the NEXT_DATE will be update correctly to next SUNDAY.

[pnrdba]

I'm sorry jurij, forgot to put a commit there. Here is the code i used with commit , but still getting the same out put.

SQL> variable jobno number
SQL> begin
2 DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',sysdate,
3 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24');
4 commit;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> print jobno

JOBNO
----------
41

SQL> select THIS_DATE,THIS_SEC,NEXT_DATE, NEXT_SEC,INTERVAL from user_jobs where job=41;

THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC
--------- -------- --------- --------
INTERVAL
--------------------------------------------------------------------------------
09-JUL-02 05:01:51 09-JUL-02 05:01:47
NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+3/24


Here it's still showing next_date as today's date only. Did i do any mistake again?? Please help me

[jmodic]

Your job is running just now! THIS_DATE and THIS_SEC columns are not null only during the execution of the job. And during that time, NEXT_DATE and NEXT_SEC are set to the same values as THIS_DATE and THIS_SEC.

As soon as the job completes, the values in THIS_DATE/THIS_SEC will disappear and the NEXT_DATE/NEXT_SEC will be set to the next sunday.

[pnrdba]

Jurij,
Please help me!!!

[jmodic]

I bet your job is repeating itself because there are unhandeled exceptions in your procedure. What does the following query return:

SELECT failures, total_time, last_sec, this_sec, next_sec last_date, next_date FROM user_jobs
WHERE job_id = 41; -- or whatever your job number is

[shailendra]

See there are two parameter in the job and you are passing sysdate and the 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24');

so in case of first parameter which is sysdate it will fire as soon as you run that job and the second parameter is the due date parameter so do one thing put this string(NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24) in both the parameter

begin
DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',
NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24,
'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+3/24');
end;

Hope this will work

Shailendra

[pnrdba]

Jurij,
This is the output. I got 1 failure. What can be derived from this???


1 SELECT failures, total_time, last_sec, this_sec, next_sec last_date, next_date FROM user_jobs
2* WHERE job = 44
3 /

FAILURES TOTAL_TIME LAST_SEC THIS_SEC LAST_DAT NEXT_DATE
--------- ---------- -------- -------- -------- ---------
1 247 07:25:33 07:30:15 07:30:14 09-JUL-02

[pnrdba]

SQL> select job from dba_jobs_running;

no rows selected

SQL> select THIS_DATE,THIS_SEC,NEXT_DATE, NEXT_SEC,INTERVAL from user_jobs where job=44;

THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC
--------- -------- --------- --------
INTERVAL
--------------------------------------------------------------------------------
09-JUL-02 07:47:54
NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+3/24

Jurij,
now my job is not running it seems, atleast now, it has to show me the next sunday's date. but here it's not. And i gone throught the complete procedure there is no exceptions which are unhandled. What might be the problem??