Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_job/scheduler
DBMS_job/scheduler [message #363055] Thu, 04 December 2008 23:42 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
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 #363097 is a reply to message #363073] Fri, 05 December 2008 01:45 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
Thank u so much.
And Do I need Admin access for the same to implement the dbms jobs in the oracle 10g Express edition?

Thanks,
Sowmya
Re: DBMS_job/scheduler [message #363104 is a reply to message #363097] Fri, 05 December 2008 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You need execute privilege on dbms_job package (and on the procedures you call of course). Prerequisites are in the link I posted.

Regards
Michel
Re: DBMS_job/scheduler [message #363140 is a reply to message #363104] Fri, 05 December 2008 03:48 Go to previous messageGo to next message
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 #363145 is a reply to message #363140] Fri, 05 December 2008 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'05/12/2008 01:40:00'
This is NOT a date expression, this is a string.

Regards
Michel
Re: DBMS_job/scheduler [message #363153 is a reply to message #363145] Fri, 05 December 2008 04:26 Go to previous messageGo to next message
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 #363155 is a reply to message #363153] Fri, 05 December 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is this: "05-12-08+1"? How do you understand it? How Oracle can understand it?
For myself: 05-12-08+1= -14 which is hardly a date.

Regards
Michel
Re: DBMS_job/scheduler [message #363157 is a reply to message #363153] Fri, 05 December 2008 04:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #363171 is a reply to message #363169] Fri, 05 December 2008 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first parameter is an OUT one, you correctly use it in your previous posts.

Regards
Michel
Re: DBMS_job/scheduler [message #363173 is a reply to message #363055] Fri, 05 December 2008 05:15 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
it was really surprising that Micheal didn't ask for formating the post. Smile
Re: DBMS_job/scheduler [message #363177 is a reply to message #363173] Fri, 05 December 2008 05:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From time to time I'm tired and disheartened to repeat the same thing, but if you encourage me I can post it.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: DBMS_job/scheduler [message #363181 is a reply to message #363171] Fri, 05 December 2008 05:24 Go to previous messageGo to next message
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
Re: DBMS_job/scheduler [message #363185 is a reply to message #363181] Fri, 05 December 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
whether the below one is correct?

No otherwise it would not return an exception
Quote:
And one more doubt, Whether the job number should be added to that before.

It is returned by the procedure.
Quote:
Other that that, whether the below code works?

How is this different from the first question?
You can see it does not.

Acually I don't understand what you want with this (unformatted) post.

Regards
Michel
Re: DBMS_job/scheduler [message #363190 is a reply to message #363185] Fri, 05 December 2008 05:33 Go to previous messageGo to next message
sowmyaa
Messages: 26
Registered: November 2008
Junior Member
sorry for this inconvenience!
Re: DBMS_job/scheduler [message #363191 is a reply to message #363055] Fri, 05 December 2008 05:47 Go to previous message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
please go through this http://www.orafaq.com/node/871
this will help you out.
Previous Topic: UTL_FILE.FOPEN got ORA-29283 [merged and dupe removed]
Next Topic: Calling external interface procedure
Goto Forum:
  


Current Time: Tue Dec 06 11:55:32 CST 2016

Total time taken to generate the page: 4.34238 seconds