|
|
|
|
Re: To run DML every hour in PL/SQL [message #439614 is a reply to message #439609] |
Mon, 18 January 2010 12:31   |
joy_division
Messages: 4963 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 #439618 is a reply to message #439615] |
Mon, 18 January 2010 12:39   |
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 #439630 is a reply to message #439629] |
Mon, 18 January 2010 13:18   |
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 #439654 is a reply to message #439634] |
Mon, 18 January 2010 23:58   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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  |
 |
Littlefoot
Messages: 21823 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.
|
|
|