Menu Request Demo

Running an Oracle Procedure when a RED Job Completes with a Linux Scheduler

Date:
07 April 2011
Author:
Jason Laws

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
) AS
v_result INTEGER;
BEGIN
v_result := WsParameterWrite('TEST_IT', '1', 'Test');
RETURN;
END dss_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!

CATEGORIES:

Comments 0

Leave a Comment

No comments.