DBMS_JOB not running, urgent please help

Hi Guys,
I created a job by using DBMS_JOB.SUBMIT(....);
when i say print jobno:
JOBNO
------
21

Now i want to run this job no 21 on every sunday morning 03:00:00 am. i tried it using dbms.next_date(sysdate,'sysdate+7);
but where to give sunday and time also??

Can any one help me how to put my job to run on every sunday ,please.

It would be very helpful if i can get sequence of steps. becuase i've to do it on production which in turn calls some other packages.

Az interval value, set 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY"")+3/24'

Hi Try this,

DBMS_JOB.SUBMIT (:v_jobnum,'my_procedure;', sysdate,
'TRUNC(NEXT_DAY(SYSDATE,''SUNDAY'')+3/24');


Shaz

Guys,
This is not wroking, i tried in both the ways. Here i'm pasting the eror messages:

PROD>>VARIABLE jobno NUMBER
PROD>>begin
2 DBMS_JOB.SUBMIT(:jobno,
3 'CALL_WEEKLY;',
4 SYSDATE,'SYSDATE + 7');
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

PROD>>begin
2 DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',
3 sysdate,
4 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY"")+3/24');
5 end;
6 /
begin
*
ERROR at line 1:
ORA-23319: parameter value "NEXT_DAY(TRUNC(SYSDATE),'SUNDAY"")+3/24" is not
appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 132
ORA-06512: at line 2


PROD>>VARIABLE jobno NUMBER
PROD>>begin
2 DBMS_JOB.SUBMIT(:jobno, 'CALL_WEEKLY;',
3 sysdate,
4 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY"")+3/24');
5 end;
6 /
begin
*
ERROR at line 1:
ORA-23319: parameter value "NEXT_DAY(TRUNC(SYSDATE),'SUNDAY"")+3/24" is not
appropriate
ORA-06512: at "SYS.DBMS_JOB", line 57
ORA-06512: at "SYS.DBMS_JOB", line 132
ORA-06512: at line 2

Please figure out my problem, thanks in advance

execute this command

begin
ret number;
DBMS_JOB.SUBMIT(ret, 'CALL_WEEKLY;',sysdate,
'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY")+3/24');
commit;
end;


Hope this will work
Shailendra

Thanks for your reply Shailendra,
But this

execute this command

begin
ret number;
DBMS_JOB.SUBMIT(ret, 'CALL_WEEKLY;',sysdate,
'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY")+3/24');
commit;
end;

is also not working....can you please help me with any other solution..

Guys, Please through some light on this problem. I'm still struggling with this.

Thanks in advance

Has you got an error?
Otherwise if nothing happens you should check
the init.ora param for queuung

Code:

---

job_queue_processes -- greater 0
job_queue_intervall -- interval in seconde

---

Orca

Pnrdba,

There has been a typo in Julian's message that you have addopted in all of your following tries. There should be two single quote characters around SUNDAY, before and after it. In Julian's message there are two single quotes before and two double quotes after the name of the day. So the following should work for you (if you have NLS_LANGUAGE set to AMERICAN):

Code:

---

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

---

10x Jurij! Yes, there was a typo.

To pnrdba: Adopt the code above (by J.M.).

Thanks a lot Jurij,
I'll try it now, and get back to you.

Thanks once again

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???

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

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.

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

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.

Jurij,
Please help me!!!

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

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

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

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??

As I said, it means that you have some errors in your procedure CALL_WEEKLY, so that procedure does not run successfully. When this happens, Oracle does not compute NEXT_DATE parameter from your INTERVAL setting. Instead it reschedule the job with a short delay. If also the second time the procedure runs unsuccessfully the job is reschedule with a longer delay. This repeats up to 16 times, allways with doubled delayed time. After 16th failure the job is marked as BROKEN.

Only when a job is run successfully the setting of NEXT_DATE is set according to your interval parameter.

In your case you must find why your procedure runs unsuccessfuly, correct the error and reschedule the job. To find out what is the problem with your procedure, run it manually. In the meantime you better remove that crippled job from your job queue with DBMS_JOB.REMOVE procedure (and don't forget to commit!).

Thank you jurij,
i tried it executing manually, but got no errors. It ran for once successfully.

Hi Jurij,
Its showing one failure and i pasted the out put over here. Please look into the out put and suggest me what to do , the procedure has no errors and ran successfully when i executed it manually.

Thanks and regards

Hi ,
I posted the output of the query what you have given jurij. I got one failure and the job is still running. What to do with this??

Hi,
i do not want to be unpolite;
You got a lot of help;
But in my mind the discussion exceeds the limit.

Please refer Oracle-Documentation and serarch-possibilities in Internet or visit an administration-course.

Orca

Ok, Thanks for your politeness. I'll do that.

Quote:

---

Originally posted by Orca777
Hi,
i do not want to be unpolite;
You got a lot of help;
But in my mind the discussion exceeds the limit.

Please refer Oracle-Documentation and serarch-possibilities in Internet or visit an administration-course.

Orca

---

Hi PNRDBA!
Sorry for my last posting!
I do not have the right to do this!
please forgive me!
Knowledge made me proud

but

love makes humble.

Orca


[Edited by Orca777 on 07-10-2002 at 04:03 PM]

Get a little taste of your own medicine, or did you get some last night?

"To be humble to superiors is duty, to equals courtesy, to inferiors nobleness."
-- Benjamin Franklin (1706-90)