SQL*XL: Calling Procedures and Functions

Calling Procedures and Functions

This page describes how to call PL/SQL procedures and functions in Excel using SQL*XL. PL/SQL can be executed from the SQL dialog. In the examples below, the SQL is executed from the SQL dialog.

Simple procedure and function examples:
Calling a procedure with only input parameters
Calling a procedure with input and output parameters
Calling a function

Source code for the package used in the examples above

Procedure calls returning a REF CURSOR:
Call to a procedure returning a ref cursor
Calling a procedure with input parameters, returning a ref cursor

Source code for the package used in the examples above

Calling a procedure with only input parameters: (top)

begin
ProcTest.ProcInput( 'Hello world' );
end;
Calling a procedure with input and output parameters: (top)
This procedure will take two input numbers which will be multiplied. The third procedure parameter is an output parameter with the resulting number. In the example below a bind variable to the Excel cell A20 is used. The result will be put in A20 by SQL*XL.

begin
ProcTest.ProcMultiply( 2, 3, :A20);
end;
Calling a function: (top)
This function will take two input numbers which will be multiplied. The resulting number is returned. In the example below a bind variable to the Excel cell A25 is used to put. The result will be put in A25 by SQL*XL.

begin
:A25 := ProcTest.FuncMultiply( 2, 3 );
end;
Calling a procedure returning a REF CURSOR: (top)
The recordset_from_proc SQL*XL function is used to get an output REF CURSOR from a stored procedure. A question mark is used as a placeholder to indicate the position of the output ref cursor.

select RECORDSET_FROM_PROC("EmpPack.GetEmpData(?)");
Calling a procedure with input parameters, returning a REF CURSOR: (top)
Input parameters can be mixed with the ref cursor output variable as shown in this example.

select RECORDSET_FROM_PROC("EmpPack.GetEmp( 7499,? )");

Package used for the simpel procedure and function call examples: (top )

CREATE OR REPLACE PACKAGE ProcTest
AS
   PROCEDURE ProcInput (Text IN VARCHAR);
   PROCEDURE ProcMultiply (Number1 IN NUMBER, Number2 IN NUMBER, Result OUT NUMBER);
   FUNCTION FuncMultiply (Number1 IN NUMBER, Number2 IN NUMBER) Return NUMBER;
END ProcTest;
/

CREATE OR REPLACE PACKAGE BODY ProcTest
AS
   PROCEDURE ProcInput (Text IN VARCHAR)
   AS
   BEGIN
      Insert into TempText (DebugText) values (Text);
      COMMIT;
   END;

   PROCEDURE ProcMultiply (Number1 IN NUMBER, Number2 IN NUMBER, Result OUT NUMBER)
   AS
   BEGIN
      Result := Number1 * Number2;
   END;

   FUNCTION FuncMultiply (Number1 IN NUMBER, Number2 IN NUMBER) Return NUMBER
   AS
   BEGIN
      RETURN Number1 * Number2;
   END;
END ProcTest;
/

Package used for the ref cursor procedure call examples: (top )

CREATE OR REPLACE PACKAGE EmpPack
AS
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;

   PROCEDURE GetEmpData (emp_cv OUT EmpCurTyp);
   PROCEDURE GetEmp (employee IN number, emp_cv OUT EmpCurTyp);
END EmpPack;
/

CREATE OR REPLACE PACKAGE BODY EmpPack
AS
   PROCEDURE GetEmpData (emp_cv OUT EmpCurTyp)
   AS
   BEGIN
      OPEN emp_cv FOR select * from emp;
   END;

   PROCEDURE GetEmp (employee IN number, emp_cv OUT EmpCurTyp)
   AS
   BEGIN
      OPEN emp_cv FOR select * from emp where empno = Employee;
   END;
END EmpPack;
/