| DBMS_job/scheduler [message #363055] |
Thu, 04 December 2008 23:42  |
sowmyaa
Messages: 26
Registered: November 2008
|
Junior Member |
|
|
Hi,
I have a package with 5 procedure in it.I want the package to run every week automatically on specific time.
And As I don't have any idea on this DBMS_scheduler/job, it's really very tuff for me to do this.
Pls help me for the solution with the sample DBMS job/scheduler.
NOTE:AS I cannot run the whole package at the same time, how can i run the 5 proc in this package using this dbms sch/job?
whether the below code will work, and how to mention my package.procedure name to execute it.
Dbms_Scheduler.create_job(
job_name => 'DEMO_JOB'
,job_type => 'STORED_PROCEDURE'
,job_action => 'PROC_TEST'
,start_date => SYSDATE
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'Demo for job schedule.');
END;
Thanks,
Sowmya
|
|
|
|
| Re: DBMS_job/scheduler [message #363073 is a reply to message #363055] |
Fri, 05 December 2008 00:44  |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
If you want to run it every week, the frequency is not dayly but weekly, isn't it?
For myself, I prefer dbms_job for this, for instance:
dbms_job.submit (
jobno,
'begin proc1; proc2; proc3; end;',
next_day(sysdate,'Monday')+5/24,
'next_day(sysdate+1,''Monday'')+5/24'
);
Regards
Michel
|
|
|
|
|
|
|
|
| Re: DBMS_job/scheduler [message #363140 is a reply to message #363104] |
Fri, 05 December 2008 03:48 |
sowmyaa
Messages: 26
Registered: November 2008
|
Junior Member |
|
|
Thanks!
And Could you please clarify the below one if pos,I pasted the from the sql screen.
There is a procedure called 'p_ti' which displays the time.
And I have created the job which calls the procedure on the mentioned date and time.But I'm getting the error.I coud not able to find it out.
(Note:I want it to be calld every one hour now and tomm and then later on weekly,for that where should i change for weekly value)
SQL> CREATE OR REPLACE PROCEDURE P_TI IS V_CURRENTTIME VARCHAR2(10000);
2 BEGIN
3 SELECT TO_CHAR(SYSDATE, 'DY DD-MON-YYYY HH24:MI:SS') INTO V_CURRENTTIME FROM DUAL;
4 DBMS_OUTPUT.PUT_LINE('CURRENT TIME' || V_CURRENTTIME);
5 END P_TI;
6 /
Procedure created.
SQL> EXECUTE P_TI
CURRENT TIMEFRI 05-DEC-2008 01:36:01
PL/SQL procedure successfully completed.
SQL>
SQL> VARIABLE jobno NUMBER;
SQL> BEGIN
2 DBMS_JOB.SUBMIT (
3 job => :jobno
4 ,what => 'BEGIN T_PI;END;'
5 ,next_date => '05/12/2008 01:40:00'
6 ,interval => 'TRUNC(SYSDATE) + 1 + 21/24'
7 );
8 END;
9 /
DBMS_JOB.SUBMIT (
*
ERROR at line 2:
ORA-01843: not a valid month
ORA-06512: at line 2
Thank u so much Michel for your helpful and effective reply!
Thanks,
Sowmya
|
|
|
|
|
|
| Re: DBMS_job/scheduler [message #363153 is a reply to message #363145] |
Fri, 05 December 2008 04:26 |
sowmyaa
Messages: 26
Registered: November 2008
|
Junior Member |
|
|
Michel,
Thanks fo your timely reply.
I got the error as follow.I have given the job number,procedure name and date(as today's date) and next date to be tommorrow (today+1).
But I have not mentioned the time exactly( is 5/24 refers to that but no minutes and seconds)? and how to metnion the wekly/daily?
SQL> BEGIN
2 DBMS_JOB.SUBMIT(12300,'BEGIN T_PI;END;',
3 NEXT_DAY(05-12-08,'MONDAY')+5/24,
4 'NEXT_DAY(05-12-08+1,''MONDAY'')+5/24');END;
5 /
NEXT_DAY(05-12-08,'MONDAY')+5/24,
*
ERROR at line 3:
ORA-06550: line 3, column 1:
PLS-00306: wrong number or types of arguments in call to 'NEXT_DAY'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
Thanks & regds,
Sowmya
|
|
|
|
|
|
| Re: DBMS_job/scheduler [message #363157 is a reply to message #363153] |
Fri, 05 December 2008 04:33 |
joicejohn
Messages: 327
Registered: March 2008
Location: India
|
Senior Member |
|
|
@sowmyaa,
| sowmyaa wrote on Fri, 05 December 2008 15:56 |
NEXT_DAY(05-12-08,'MONDAY')+5/24,
|
I think Michel pointed it out already:
| Michel Cadot wrote on Fri, 05 December 2008 15:28 |
'05/12/2008 01:40:00'
This is NOT a date expression, this is a string.
|
The same answer goes for the above quote even if you used '-' instead of '/'.
Use TO_DATE Function to convert the string to date and then use that in the NEXT_DAY Function.
Hope this helps.,
Regards,
Jo
[Updated on: Fri, 05 December 2008 04:35] Report message to a moderator |
|
|
|
| Re: DBMS_job/scheduler [message #363169 is a reply to message #363155] |
Fri, 05 December 2008 05:11 |
sowmyaa
Messages: 26
Registered: November 2008
|
Junior Member |
|
|
Sorry michel,
As I am totally new to the dbms job/scheduler.I am getting errors,
Whether any steps should be done before creating this job.I created only the procedure "T_PI" and then started to run this job script. And I got the error as follow,
SQL> BEGIN
2 DBMS_JOB.SUBMIT(12300,'BEGIN T_PI;END;',TRUNC(SYSDATE+1),
3 'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')+5/24');
4 END;
5 /
DBMS_JOB.SUBMIT(12300,'BEGIN T_PI;END;',TRUNC(SYSDATE+1),
*
ERROR at line 2:
ORA-06550: line 2, column 17:
PLS-00363: expression '12300' cannot be used as an assignment target
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
Thanks,
Sowmya
|
|
|
|
|
|
|
|
|
|
| Re: DBMS_job/scheduler [message #363181 is a reply to message #363171] |
Fri, 05 December 2008 05:24 |
sowmyaa
Messages: 26
Registered: November 2008
|
Junior Member |
|
|
Thanks for your reply Michel,
Actually I am confused with this one now.
whether the below one is correct? And one more doubt, Whether the job number should be added to that before.
Other that that, whether the below code works?
It was very very helpful for me with you immediate and correct response
SQL> BEGIN
2 DBMS_JOB.SUBMIT(12300,'BEGIN T_PI;END;',
3 '05-12-08,01:40:00',
4 'TRUNC(SYSDATE)+1+5/24');END;
5 /
DBMS_JOB.SUBMIT(12300,'BEGIN T_PI;END;',
*
ERROR at line 2:
ORA-06550: line 2, column 18:
PLS-00363: expression '12300' cannot be used as an assignment target
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
Thanks Sowmya
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
