Oracle Data Adapter Stored Procedure Support

The Oracle Data Adapter allows you to use Direct SQL Passthru to call an Oracle stored procedure using any necessary input parameters. These procedures need to be developed within Oracle using the CREATE PROCEDURE command. They can either return an answer set or a return code indicating the result of the invocation when no data is returned. When an answer set is returned, the data adapter creates a temporary Master File named SQLOUT that can be used to generate reports against the answer set.

How to: Invoke an Oracle Stored Procedure Set a Maximum Number of Input Parameters for Oracle Stored Procedures Example: Sample Oracle Stored Procedure Invoking an Oracle Stored Procedure That Returns an Answer Set Invoking an Oracle Stored Procedure That Returns a Return Code Reference: Rules for Oracle Stored Procedures

Note: Oracle stored procedure support only allows the processing of one answer set per invocation. Attempts to retrieve multiple answer sets generate an error message.

Syntax: How to Invoke an Oracle Stored Procedure

SQL SQLORA EX spname  parm1,parm2,...,parmn;

where:

spname

Is the Oracle stored procedure, it is of the form:

packagename.procedurename

parmn

Are the parameter values to be supplied as input for the procedure and must be scalar in type. That is, only single values are acceptable as opposed to vector or array types. Non-supported parameter types will generate an error message.

Reference: Rules for Oracle Stored Procedures

• Only scalar input parameters are allowed, but not required.
• Output parameters are not allowed, except for optional cursor type parameters.
• A cursor used for output parameters must be defined with:
• The TYPE statement in a PACKAGE or PROCEDURE.
• An associated record layout of the answer set to be returned.

• The cursor must be opened in the procedure.
• No fetching is allowed from the output parameter cursors in the stored procedure. The Oracle Data Adapter fetches the answer set.
• Any error messages must be issued using the RAISE APPLICATION ERROR method.

Note: Any application error that is issued by the stored procedure is available in the variable &ORAMSGTXT. The variable &ORAMSGTXT can be used in conjunction with &RETCODE to process errors and/or messages that are returned by the stored procedure.

Syntax: How to Set a Maximum Number of Input Parameters for Oracle Stored Procedures

A data adapter parameter can be used to set the maximum number of input parameters.

SQL SQLORA SET SPMAXPRM value

where:

value

Is a numeric value indicating the maximum number of input parameters that may be entered for stored procedures. The default value is 256. This value is displayed by the SQL SQLORA ? query.

Example: Sample Oracle Stored Procedure

CREATE OR REPLACE PACKAGE pack1 AS
TYPE nfrectype IS RECORD (
employee NF29005.EMPLOYEE_ID5%TYPE,
ssn5     NF29005.SSN5%TYPE,
l_name   NF29005.LAST_NAME5%TYPE,
f_name   NF29005.FIRST_NAME5%TYPE,
birthday NF29005.BIRTHDATE5%TYPE,
salary   NF29005.SALARY5%TYPE,
joblevel NF29005.JOB_LEVEL5%TYPE);
TYPE nfcurtype IS REF CURSOR RETURN nfrectype ;
PROCEDURE proc1(c_saltable IN OUT nfcurtype);
END pack1 ;
/
CREATE OR REPLACE PACKAGE BODY pack1 AS
PROCEDURE proc1 (c_saltable IN OUT nfcurtype)
IS
BEGIN
OPEN c_saltable FOR SELECT EMPLOYEE_ID5,SSN5,LAST_NAME5,FIRST_NAME5,BIRTHDAT
E5,SALARY5,JOB_LEVEL5 FROM NF29005;
END proc1 ;  -- end of procedure
END pack1; -- end of package body
/

Example: Invoking an Oracle Stored Procedure That Returns an Answer Set

This example invokes a stored procedure that returns an answer set. The results can then be displayed using a TABLE request against the Master File SQLOUT that is created by the data adapter to describe the returned answer set:

SQL SQLORA EX  pkg1.tblproc  parma;
TABLE FILE SQLOUT
PRINT *
END

Example: Invoking an Oracle Stored Procedure That Returns a Return Code

In the following example, the return code from the Oracle stored procedure is inspected to determine the flow of control.

SQL SQLORA EX pkg1.insrtproc parm1;
-IF &RETCODE EQ 0 GOTO OK;
-TYPE &ORAMSGTXT
-OK;