Oracle Examples:

Callable Procedures > Oracle Examples:

Oracle Examples:

1. Oracle example of a call from a Stored Procedure:

Add the following to the declaration section of the stored procedure:

v_result integer;

v_return_code varchar2(1);

v_return_msg varchar2(256);

v_job_status_simple varchar2(1);

v_job_status_standard varchar2(1);

v_job_status_enhanced varchar2(2);

Then add the call to the procedure:

v_result := Ws_Job_Status(p_sequence, p_job_name, p_task_name, p_job_id, p_task_id

, NULL, 'Daily Run', 10, NULL

, v_return_code, v_return_msg

, v_job_status_simple, v_job_status_standard, v_job_status_enhanced);

Perhaps add a check of the result:

IF ( v_result <> 1 ) THEN

p_status := v_result;

p_return_msg := 'Failed to check job status. Return Code was ' || v_return_code;

p_return_msg := p_return_msg || '. ' || v_return_msg;

END IF;

2. Oracle example of a call from sqlplus:

Note: The line beginning exec ... and end with ; must be one line.

Place the following code in a text file called FileName.sql:

set pages 0 feed off heading off

variable res number;

variable rcode varchar2(1);

variable rmsg varchar2(256);

variable stat1 varchar2(1);

variable stat2 varchar2(1);

variable stat3 varchar2(2);

exec :res:=Ws_Job_Status(1,'Check Job','Stat',0,0,NULL,'Daily Run',10,NULL,:rcode,:rmsg,:stat1,:stat2,:stat3);