Home » SQL & PL/SQL » SQL & PL/SQL » To run DML every hour in PL/SQL (Oracle 9.2.0.3)
To run DML every hour in PL/SQL [message #439609] Mon, 18 January 2010 12:25 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I want to run  at a particualr interval in a day .Whats the better way to achieve this ?  one parameter I have added
in_interval varchar2 default 'HH'

while   ( v_end_dt <=   v_begin_dt)
    loop  
      Insert into..
    end loop;

[Updated on: Mon, 18 January 2010 12:26]

Report message to a moderator

Re: To run DML every hour in PL/SQL [message #439610 is a reply to message #439609] Mon, 18 January 2010 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
>Whats the better way to achieve this ?
DBMS_JOB

SQL> desc dbms_job
FUNCTION BACKGROUND_PROCESS RETURNS BOOLEAN
PROCEDURE BROKEN
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 BROKEN 			BOOLEAN 		IN
 NEXT_DATE			DATE			IN     DEFAULT
PROCEDURE CHANGE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 WHAT				VARCHAR2		IN
 NEXT_DATE			DATE			IN
 INTERVAL			VARCHAR2		IN
 INSTANCE			BINARY_INTEGER		IN     DEFAULT
 FORCE				BOOLEAN 		IN     DEFAULT
PROCEDURE INSTANCE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 INSTANCE			BINARY_INTEGER		IN
 FORCE				BOOLEAN 		IN     DEFAULT
PROCEDURE INTERVAL
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 INTERVAL			VARCHAR2		IN
PROCEDURE ISUBMIT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 WHAT				VARCHAR2		IN
 NEXT_DATE			DATE			IN
 INTERVAL			VARCHAR2		IN     DEFAULT
 NO_PARSE			BOOLEAN 		IN     DEFAULT
FUNCTION IS_JOBQ RETURNS BOOLEAN
PROCEDURE NEXT_DATE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 NEXT_DATE			DATE			IN
PROCEDURE REMOVE
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
PROCEDURE RUN
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 FORCE				BOOLEAN 		IN     DEFAULT
PROCEDURE SUBMIT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		OUT
 WHAT				VARCHAR2		IN
 NEXT_DATE			DATE			IN     DEFAULT
 INTERVAL			VARCHAR2		IN     DEFAULT
 NO_PARSE			BOOLEAN 		IN     DEFAULT
 INSTANCE			BINARY_INTEGER		IN     DEFAULT
 FORCE				BOOLEAN 		IN     DEFAULT
PROCEDURE USER_EXPORT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 MYCALL 			VARCHAR2		IN/OUT
PROCEDURE USER_EXPORT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 MYCALL 			VARCHAR2		IN/OUT
 MYINST 			VARCHAR2		IN/OUT
PROCEDURE WHAT
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB				BINARY_INTEGER		IN
 WHAT				VARCHAR2		IN
Re: To run DML every hour in PL/SQL [message #439611 is a reply to message #439610] Mon, 18 January 2010 12:28 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Instead of using DBMS_JOB, I want to pass a parameter to take input for interval of time in hour and to run for a day at ineteval.

Thanks
Re: To run DML every hour in PL/SQL [message #439613 is a reply to message #439611] Mon, 18 January 2010 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read DBMS_JOB and click on "DBMS_JOB complex scheduling".

Regards
Michel

[Updated on: Mon, 18 January 2010 12:30]

Report message to a moderator

Re: To run DML every hour in PL/SQL [message #439614 is a reply to message #439609] Mon, 18 January 2010 12:31 Go to previous messageGo to next message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
You're in 9i, so you'll need to use DBMS_JOB instead of DBMS_SCHEDULER, but I am not sure what you wish to achieve with a condition like
while   ( v_end_dt <=   v_begin_dt)


This will run forever or not at all.

[added] Heh, see what happens when you eat lunch while answering questions? Too slow. I see that there were more answers, plus OP added some info into his post.

[Updated on: Mon, 18 January 2010 12:32]

Report message to a moderator

Re: To run DML every hour in PL/SQL [message #439615 is a reply to message #439611] Mon, 18 January 2010 12:32 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps that is what you want, but it doesn't mean that it is the right way to do that. DBMS_JOB (or DBMS_SCHEDULER), on the contrary, is how scheduling should be done.
Re: To run DML every hour in PL/SQL [message #439617 is a reply to message #439611] Mon, 18 January 2010 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
> I want to pass a parameter to take input for interval of time in hour and to run for a day at ineteval.
& waste CPU cycles constantly trying to decide when to actually do something.
Re: To run DML every hour in PL/SQL [message #439618 is a reply to message #439615] Mon, 18 January 2010 12:39 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I need to pass three parameter like in_begin_dt, in_end_dt and
the time interval for which it should execute the insert statement in the loop.
in_interval varchar2 default 'HH'
If I pass interval then it should execute the insert statement within the loop at the interval I am passing for the day ( diff in_begin_dt, in_end_dt)
Re: To run DML every hour in PL/SQL [message #439619 is a reply to message #439618] Mon, 18 January 2010 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
>I need to pass three parameter like in_begin_dt, in_end_dt and
So exactly why won't you use DBMS_JOB?
Re: To run DML every hour in PL/SQL [message #439621 is a reply to message #439619] Mon, 18 January 2010 12:45 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
The interval may be different as per the paramater passed by user.
Re: To run DML every hour in PL/SQL [message #439624 is a reply to message #439621] Mon, 18 January 2010 12:48 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
>The interval may be different as per the paramater passed by user.
INTERVAL is an argument of DBMS_JOB.SUBMIT
Re: To run DML every hour in PL/SQL [message #439628 is a reply to message #439624] Mon, 18 January 2010 13:02 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
if some error occured DBMS_JOB wont execute. Instead of it I want to pass interval say 1 and it should execute the insert statement(it has timestamp interval) after every 1 hour
Re: To run DML every hour in PL/SQL [message #439629 is a reply to message #439628] Mon, 18 January 2010 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
>I want to pass interval say 1 and it should execute the insert statement(it has timestamp interval) after every 1 hour
Since you insist, proceed to do so.
Nobody here can stop you from deploying suboptimal solutions.
Re: To run DML every hour in PL/SQL [message #439630 is a reply to message #439629] Mon, 18 January 2010 13:18 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
v_from_dte + interval '1' hour <= v_to_dte
diff of v_from_dte and v_to_date should always be 1
Is there a way
to make the '1' hour part dynamic



for example
if I pass in_interval_type 'HH' and in_value say 2 it should
pass the value in pl/sql and make it


v_from_dte + interval '2' hour <= v_to_dte


if in_interval_type 'MI' then it should accept 15,30,45,60
and change it to

v_from_dte + interval '15' min <= v_to_dte



Re: To run DML every hour in PL/SQL [message #439631 is a reply to message #439630] Mon, 18 January 2010 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 24906
Registered: January 2009
Senior Member
>Is there a way to make the '1' hour part dynamic
It is YOUR code.
You are free to write it to meet your requirements; what ever they might be.
Re: To run DML every hour in PL/SQL [message #439632 is a reply to message #439630] Mon, 18 January 2010 13:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And please read the links I provided you might be able to easily do what you want.

Regards
Michel
Re: To run DML every hour in PL/SQL [message #439634 is a reply to message #439631] Mon, 18 January 2010 13:30 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thank you very much! My question was if I can do it without DBMS_JOB
Re: To run DML every hour in PL/SQL [message #439637 is a reply to message #439609] Mon, 18 January 2010 14:27 Go to previous messageGo to next message
Bill B
Messages: 1457
Registered: December 2004
Senior Member
If you insist on doing it the absolute wrong way, do NOT sit there spinning. See the following procedure.

DBMS_LOCK.SLEEP
Re: To run DML every hour in PL/SQL [message #439654 is a reply to message #439634] Mon, 18 January 2010 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63805
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unless you use a (possibly external) scheduler, this mean you have an always active session.
Can you ensure this will be true? What do you want it happens when the instance shut down? Does the work still should be done or not?

Regards
Michel

[Updated on: Tue, 19 January 2010 01:10]

Report message to a moderator

Re: To run DML every hour in PL/SQL [message #439668 is a reply to message #439628] Tue, 19 January 2010 00:55 Go to previous message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
prachij593 wrote on Mon, 18 January 2010 20:02
if some error occured DBMS_JOB wont execute. Instead of it I want to pass interval say 1 and it should execute the insert statement(it has timestamp interval) after every 1 hour

An add-on to Michel's message: if "some error" occurs (such as shutdown), your procedure will fail and nothing will resurrect it (i.e. you'll have to do it manually). A scheduled job will run automatically next time it is scheduled, without your intervention.
Previous Topic: Accessing Constants from a package
Next Topic: utl_file_dir parameter in oracle10G
Goto Forum:
  


Current Time: Tue Sep 27 00:46:42 CDT 2016

Total time taken to generate the page: 0.11007 seconds