Home » SQL & PL/SQL » SQL & PL/SQL » Option to keep running a PL/SQL package automatically
Option to keep running a PL/SQL package automatically [message #317353] Wed, 30 April 2008 16:38 Go to next message
rasa
Messages: 45
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 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: 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] by Moderator

Report message to a moderator

Re: Option to keep running a PL/SQL package automatically [message #317356 is a reply to message #317355] Wed, 30 April 2008 16:56 Go to previous messageGo to next message
rasa
Messages: 45
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]

Report message to a moderator

Re: Option to keep running a PL/SQL package automatically [message #317358 is a reply to message #317353] Wed, 30 April 2008 17:08 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: 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 Go to previous messageGo to next message
rasa
Messages: 45
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
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 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
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
Previous Topic: DML Statement in Function
Next Topic: generate fixed format file
Goto Forum:
  


Current Time: Mon Dec 05 12:40:07 CST 2016

Total time taken to generate the page: 0.05925 seconds