Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

Related LIT software: litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe

Home Products SQL*XL Help Features

Buy now

Download now

SQL*XL online help

 

SQL*XL: Calling Procedures and Functions

Calling Procedures and Functions

SQL*XL
home

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;
/


See also:

SQL*XL ribbon in Excel