| Option to keep running a PL/SQL package automatically [message #317353] |
Wed, 30 April 2008 16:38  |
rasa
Messages: 30
Registered: February 2006 |
Member |
|
|
I have a PL/SQL package that processes a bunch of files. I would like to have this run so that files are processed ASAP. Therefore, scheduling this under DBMS_SCHEDULER or DBMS_JOB is not an option. I can keep looping around and whenever I see files, I can process them. No problem.
But, how can I make the package start on its own every time the database is shutoff and restarted? Also, how can the package be suspended for its execution, if the DBA wants to bring down the DB?
Am I even right in thinking along these lines? Please help with your advice. Thanks in advance.
|
|
|
| Re: Option to keep running a PL/SQL package automatically [message #317355 is a reply to message #317353 ] |
Wed, 30 April 2008 16:49  |
anacedent
Messages: 5022
Registered: July 2005
Location: surf meets turf in SoCal |
Senior Member |
|
|
>Am I even right in thinking along these lines?
In My Opinion, NO!
While a shovel is a great tool for making a hole in the ground,
it is suboptimal for screwing in a screw.
When your only tool is a hammer, all problems are treated as they are nails.
Oracle & PL/SQL should NOT be the only options considered.
Some OS operation results in the files occuring & so this procedure should invoke the PL/SQL package
& it would simplify implementation if the filename to be processed was an input parameter.
[Updated on: Wed, 30 April 2008 16:50]
|
|
|
| Re: Option to keep running a PL/SQL package automatically [message #317356 is a reply to message #317355 ] |
Wed, 30 April 2008 16:56 |
rasa
Messages: 30
Registered: February 2006 |
Member |
|
|
Hmmm....There are shell jobs that dump files in batch! So, it's not one file at a time. There may be 20 or 200 files dropped at any given time. Those scripts cannot invoke the PL.SQL package as they are not to be modified. That's a requirement.
Can I use Oracle SYSTEM triggers?
[Updated on: Wed, 30 April 2008 16:58]
|
|
|
| Re: Option to keep running a PL/SQL package automatically [message #317358 is a reply to message #317353 ] |
Wed, 30 April 2008 17:08 |
anacedent
Messages: 5022
Registered: July 2005
Location: surf meets turf in SoCal |
Senior Member |
|
|
>Can I use Oracle SYSTEM triggers?
You can use anything you desire; especially if it is a mauve database.
Ready, Fire, AIM!
You might want to consider Java instead of PL/SQL.
P.S.
How will the code "know" the filename(s) to be processed?
|
|
|
| Re: Option to keep running a PL/SQL package automatically [message #317359 is a reply to message #317358 ] |
Wed, 30 April 2008 17:12 |
rasa
Messages: 30
Registered: February 2006 |
Member |
|
|
| anacedent wrote on Wed, 30 April 2008 18:08 |
How will the code "know" the filename(s) to be processed?
|
It looks at the Directory Object and will get a directory-listing (using a Java Stored Procedure) to process all of those files.
|
|
|
| Re: Option to keep running a PL/SQL package automatically [message #317397 is a reply to message #317359 ] |
Thu, 01 May 2008 00:57 |
Michel Cadot
Messages: 15226
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
You can have a job (dbms_job) that:
1/ get the file list
2/ process the files
3/ set its restart (next job time) 10 (or whatever) minutes later
Regards
Michel
|
|
|
| Re: Option to keep running a PL/SQL package automatically [message #317491 is a reply to message #317353 ] |
Thu, 01 May 2008 10:21 |
S.Rajaram
Messages: 516
Registered: October 2006
Location: United Kingdom |
Senior Member |
|
|
As ana already suggested this one looks like a straight forward cron job to me. Or am I missing something really obvious.
Regards
Raj
|
|
|
| Re: Option to keep running a PL/SQL package automatically [message #319024 is a reply to message #317353 ] |
Thu, 08 May 2008 14:18 |
Kevin Meade
Messages: 329
Registered: November 2001 |
Senior Member |
|
|
I suppose the issue of what "scheduling" mechanism to use could be decided based on where the triggering information lives. What you have here is basically a need to do work whenever there is work in a queue. To use a database oriented scheduling technique, or an O/S might be decided here on what you will use as your queue.
Being a fan of database driven process, driven by data, I might suggest you look into ADVANCED QUEUING and have your file create job tell Oracle AQ about the file. Then the problems of when to wake up the job and how to deal with failures and restarts pretty much go away.
If you are not a fan of AQ or dont' want to learn it, then you can revert to the poor man's AQ, and use triggers and database alerts.
dbms_alert
| Quote: | The DBMS_ALERT package provides support for the asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can cause itself to be notified whenever values of interest in the database are changed.
|
These are just suggestions. Don't do anything you are not ready to maintain.
Good luck, Kevin
|
|
|
]
Members
Search
Help
Register
Login
Home
