Job control object
Job control modelled on Unix: declare "job" objects, submit them, wait for them to complete. Uses DBMS_JOB to submit tasks, DBMS_ALERT for jobs to pass back status and error messages, so the calling procedure can easily track the background jobs it started.
William Robertson http://www.williamrobertson.net/code/job_ot.typ.txt
2 Comments:
Interesting Code. Its always great to see how others write code.
I have also created my own Job Scheduler Software in plsql, but used a package rather than a type declaration. What are the benefits of types over packages for things like this?
However, some things have jumped out at me from your code. Things I know I had to consider.
1. How to tell which jobs have failed and which havnt when you have multiple jobs running?
2. Have you dealt with the fact that Oracle re-submits the jobs automatically when they fail? - the user may not want this, I have had to wrap the submitted code into an anonymous block, and handle the exception, so Oracle assumed it completed normally in order to get oracle not to re-submit the job.
There looks like a whole lot more complexity built into the package, than just what is shown on the demo program. Any chance of explaining the reasoning behind using dbms_alert, and how this helps with job control?
Thanks for stopping by.
1. You would check a job object's "run_status" attribute.
2. When the submitted command fails, an exception handler captures the details and and includes them in the message sent back via DBMS_ALERT (see the sub-block in the "execute" procedure). The job's status would then change to "Failed", and its "message" attribute would contain the error stack.
The approach is to use DBMS_ALERT to maintain a communication channel of sorts between the submitted job and the process that submitted it. Rather than just setting it off and hoping for the best, checking alert logs etc, the submit() procedure passes two additional parameters containing the names to use with DBMS_ALERT.SIGNAL.
The command line actually sent to DBMS_JOB looks like this (to execute a command cmd):
job_ot.EXECUTE('BEGIN cmd; END;', receipt_alert, completed_alert);
Notice that EXECUTE is defined as:
STATIC PROCEDURE execute
( p_command VARCHAR2 -- What to execute
, p_receipt_alert VARCHAR2 -- Signal name for confirming receipt to submitting procedure
, p_completed_alert VARCHAR2 -- Signal name for confirming job completion to submitting procedure
, p_invoking_session VARCHAR2 ) -- Session ID of submitting process
As soon as it starts but before executing cmd, it sends a confirmation message back to the submitting process:
DBMS_ALERT.SIGNAL(p_receipt_alert,v_status);
Then when cmd has completed (successfully or otherwise - it traps exceptions) it sends another message before exiting:
DBMS_ALERT.SIGNAL(p_completed_alert,v_message);
This allows the submitting process to
1. confirm the submit itself was successful, the job queue is up etc
2. wait for the submitted job to complete
3. capture the error stack from the job on failure.
Note also that it does not have to start waiting immediately, so you can submit a bunch of jobs, get on with something else, and then call their wait() methods at a convenient point, since each job object remembers its own unique signal names.
Hope that makes sense.
Post a Comment
Links to this post:
Create a Link
<< Index of code examples