Home » SQL & PL/SQL » SQL & PL/SQL » Continuos job (Oracle 11)
Continuos job [message #584276] Fri, 10 May 2013 15:36 Go to next message
webluca
Messages: 2
Registered: May 2013
Junior Member
Hello everybody,

I need to create a job that run an Oracle procedure when a flag in a table is true.

My first idea was a trigger but for some reason I prefer to not use trigger.

So I thought about a procedure that is run from windows scheduler (I cant use oracle scheduler) every day at 7:00 AM and stops at midnihgt. The procedure makes an infinite loop and every 5 seconds check for the flag, if it is true the procedure runs.

What do you think? Do you know a better approach? Is 5 seconds to small? Does the infinite loop procedure stress to many the machine?

Thanks for your help.
Re: Continuos job [message #584277 is a reply to message #584276] Fri, 10 May 2013 15:40 Go to previous messageGo to next message
Littlefoot
Messages: 19347
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What would that "reason" (so that you don't want to use a trigger, which - regarding what you described - seems to be a perfect option)? Why would you rather pester your computer with an infinite loop?
Re: Continuos job [message #584278 is a reply to message #584277] Fri, 10 May 2013 15:54 Go to previous messageGo to next message
webluca
Messages: 2
Registered: May 2013
Junior Member
One reason is that the procedure must work with the same table where the flag is. I found some workaround for ORA-04091 but I don't like it...
Another rason is that the tabel is updated by the user with an application: I don't want the user to wait the end of the procedure after he saves the data.
In general I don't like to work with trigger becasue in my opinion they are "out of control" even if in most case I must use them: I work with a software that I can't modify, I can only work with its database; in many circumstances I have to work with trigger to let the user do something not provided by the software
Re: Continuos job [message #584286 is a reply to message #584278] Sat, 11 May 2013 01:06 Go to previous message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
One reason is that the procedure must work with the same table where the flag is.


You get this error ONLY if you use a row level ("for each row") trigger not a statement one and what you tell here is that you only need a statement level trigger (otherwise an external procedure can't do the job).

Quote:
in my opinion they are "out of control"


What do you mean by "out of control"?

You have to describe what you want to do in this procedure/trigger.
And, yes, every 5 seconds is too fast. Explain why you need a so small interval.

Regards
Michel
Previous Topic: Fastest method for one to one update
Next Topic: Query help - Alpha characters
Goto Forum:
  


Current Time: Fri Aug 01 06:59:09 CDT 2014

Total time taken to generate the page: 0.08432 seconds