Ever wondered how to run an Oracle Procedure when a RED Job completes with a Linux or UNIX Scheduler?
Here's a simple way that doesn't require a separate script.
The procedure I want to run is very simple:
CREATE OR REPLACE PROCEDURE dss_test
( p_sequence IN NUMBER
, p_job_name IN VARCHAR2
, p_job_id IN NUMBER
v_result := WsParameterWrite('TEST_IT', '1', 'Test');
This procedure writes a new RED parameter, just as a test. But you could get this procedure to do anything, including calling other RED APIs to restart a job if it fails, etc. The three input parameters give you all the information you need to call any RED Scheduler API from the procedure.
The all you have to do to run this procedure when a job succeeds or fails is paste this command into the job success / failure command boxes:
echo "exec SchemaName.Dss_Test($JOB_SEQ$,'$JOB_NAME$',$JOB_KEY$);" | sqlplus /
Note: If your Linux or UNIX scheduler is not running under an OS Authenticated user, you'll need to change this to:
echo "exec SchemaName.Dss_Test($JOB_SEQ$,'$JOB_NAME$',$JOB_KEY$);" | sqlplus username/password
When my job completed, I could see a new parameter called TEST_IT in RED.
That's it! Magic!