Calling an Oracle Stored Procedure Using SQL Passthru

Using SQL Passthru is supported for Oracle stored procedures. These procedures need to be developed within Oracle using the CREATE PROCEDURE command.

Note: Calling a stored procedure using SQL Passthru only allows the processing of one answer set per invocation. If multiple answer sets are expected, the CREATE SYNONYM mechanism is preferred.

Invoke a Stored Procedure

SQL SQLORA EX procname [parameter_specification1] 
[,parameter_specification2]...
END

where:

SQLORA

Is the ENGINE suffix for Oracle.

procname

Is the name of the stored procedure. It is the fully or partially qualified name of the stored procedure in the native RDBMS syntax.

parameter_specification

IN, OUT, and INOUT parameters are supported. Use the variation required by the stored procedure:

IN

Is a literal (for example, 125, 3.14, 'abcde'). You can use reserved words as input. Unlike character literals, reserved words are not enclosed in quotation marks (for example, NULL). Input is required.

OUT

Is represented as a question mark (?). You can control whether output is passed to an application by including or omitting this parameter. If omitted, this entry will be an empty string (containing 0 characters).

INOUT

Consists of a question mark (?) for output and a literal for input, separated by a slash: /. (For example: ?/125, ?/3.14, ?/'abcde'.) The out value can be an empty string (containing 0 characters).

The adapter supports invocation of stored procedures that conform to the following rules:

  • All scalar parameters (IN, OUT, and INOUT) are supported.
  • A cursor 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 in the stored procedure. The adapter fetches the answer set.
  • Any messages must be issued using the RAISE APPLICATION ERROR method.
  • If any parameters are declared as RECORD, and those parameters are associated with REFCURSOR, then when invoking the stored procedure using SQL Passthru do not specify those parameters. For an example, see An Oracle Stored Procedure With REFCURSOR.

Any application error that is issued by the stored procedure is available in the server variable &ORAMSGTXT.

An Oracle Stored Procedure With REFCURSOR

The following Oracle stored procedure uses REFCURSOR. You must create it in SQL*Plus using PL/SQL.

sqlplus scott/tiger
set serveroutput ON
   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 ;
   /
   sho error
   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
   /
   sho error
/* 
Invocation using SQL*Plus: 
   VARIABLE c1 REFCURSOR
   EXEC scott.pack1.proc1 (:c1)
   PRINT c1
  
Invocation using SQL Passthru:
   SQL SQLORA EX scott.pack1.proc1
   TABLE FILE SQLOUT
   END
*/

An Oracle Stored Procedure Without REFCURSOR

The following Oracle stored procedure does not use REFCURSOR. It includes IN and OUT parameters. 

sqlplus scott/tiger
set serveroutput ON
 
   CREATE OR REPLACE PACKAGE pk1 AS
   PROCEDURE ibi_pr1 (v_num IN NUMBER,v_rec IN OUT VARCHAR2);
   END;
   /
   sho error
   CREATE OR REPLACE PACKAGE BODY pk1 AS
   PROCEDURE ibi_pr1 (v_num IN NUMBER,v_rec IN OUT VARCHAR2)
   IS
      v_msg    VARCHAR2(25);
      v_to_chr VARCHAR2(5);
   BEGIN
      v_to_chr := TO_CHAR(v_num);
      SELECT v_to_chr||' '||v_rec INTO v_msg FROM dual;
      v_rec := v_msg;
   END ibi_pr1;
   END pk1;
   /v
   sho error
/* 
Invocation using SQL*Plus: 
   VARIABLE v_rec VARCHAR2(20)
   -- assign IN value to IN OUT parameter:
   -- BEGIN :v_rec := 'Your message!...'; END;
   BEGIN :v_rec := &1; END;
   /
   EXEC scott.pk1.ibi_pr1 (&2,:v_rec)
   PRINT v_rec
   --
 
Invocation using SQL Passthru: 
   SQL SQLORA EX scott.pk1.ibi_pr1 12345, ?/'AbCdEf';
   END
*/