DBMS_JOB not running, urgent please help

[pnrdba]

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.

[julian]

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

[sazzadur]

Hi Try this,

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


Shaz

[pnrdba]

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

[shailendra]

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

[pnrdba]

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

[Orca777]

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

job_queue_processes -- greater 0
job_queue_intervall -- interval in seconde

Orca

[jmodic]

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):

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

[julian]

10x Jurij! Yes, there was a typo.

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

[pnrdba]

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

Thanks once again