How to: |
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.
SQL SQLORA EX procname [parameter_specification1] [,parameter_specification2]... END
where:
Is the ENGINE suffix for Oracle.
Is the name of the stored procedure. It is the fully or partially qualified name of the stored procedure in the native RDBMS syntax.
IN, OUT, and INOUT parameters are supported. Use the variation required by the stored procedure:
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.
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).
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:
Any application error that is issued by the stored procedure is available in the server variable &ORAMSGTXT.
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 */
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
*/
iWay Software |