-
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.
PNRDBA
-
Az interval value, set 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY"")+3/24'
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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
PNRDBA
-
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
PNRDBA
-
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;
/
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
10x Jurij! Yes, there was a typo.
To pnrdba: Adopt the code above (by J.M.).
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Thanks a lot Jurij,
I'll try it now, and get back to you.
Thanks once again
PNRDBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|