REFCURSOR Bind Variables

REFCURSOR bind variables can also be used to reference PL/SQL cursor variables in stored procedures. This allows you to store SELECT statements in the database and reference them from SQL*Plus.

A REFCURSOR bind variable can also be returned from a stored function.

Note: You must have Oracle7, Release 7.3 or above to assign the return value of a stored function to a REFCURSOR variable.

Example 3-18 Creating, Referencing, and Displaying REFCURSOR Bind Variables

To create, reference and display a REFCURSOR bind variable, first declare a local bind variable of the REFCURSOR datatype

SQL> VARIABLE dept_sel REFCURSOR

Next, enter a PL/SQL block that uses the bind variable in an OPEN ... FOR SELECT statement. This statement opens a cursor variable and executes a query. See the PL/SQL User's Guide and Reference for information on the OPEN command and cursor variables.

In this example we are binding the SQL*Plus dept_sel bind variable to the cursor variable.

SQL> BEGIN
  2    OPEN :dept_sel FOR SELECT * FROM DEPT;
  3  END;
  4  /

PL/SQL procedure successfully completed.

The results from the SELECT statement can now be displayed in SQL*Plus with the PRINT command.

SQL> PRINT dept_sel

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

The PRINT statement also closes the cursor. To reprint the results, the PL/SQL block must be executed again before using PRINT.

Example 3-19 Using REFCURSOR Variables in Stored Procedures

A REFCURSOR bind variable is passed as a parameter to a procedure. The parameter has a REF CURSOR type. First, define the type.

SQL> CREATE OR REPLACE PACKAGE cv_types AS
  2    TYPE DeptCurTyp is REF CURSOR RETURN dept%ROWTYPE;
  3  END cv_types;
  4  /

Package created.

Next, create the stored procedure containing an OPEN ... FOR SELECT statement.

SQL> CREATE OR REPLACE PROCEDURE dept_rpt
  2   (dept_cv IN OUT cv_types.DeptCurTyp) AS
  3  BEGIN
  4    OPEN dept_cv FOR SELECT * FROM DEPT;
  5  END;
  6  /

Procedure successfully completed.

Execute the procedure with a SQL*Plus bind variable as the parameter.

SQL> VARIABLE odcv REFCURSOR
SQL> EXECUTE dept_rpt(:odcv)

PL/SQL procedure successfully completed.

Now print the bind variable.

SQL> PRINT odcv

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

The procedure can be executed multiple times using the same or a different REFCURSOR bind variable.

SQL> VARIABLE pcv REFCURSOR
SQL> EXECUTE dept_rpt(:pcv)

PL/SQL procedure successfully completed.

SQL> PRINT pcv

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Example 3-20 Using REFCURSOR Variables in Stored Functions

Create a stored function containing an OPEN ... FOR SELECT statement:

SQL> CREATE OR REPLACE FUNCTION dept_fn RETURN -
>      cv_types.DeptCurTyp IS
  2    resultset cv_types.DeptCurTyp;
  3  BEGIN
  4    OPEN resultset FOR SELECT * FROM DEPT;
  5    RETURN(resultset);
  6  END;
  7  /
Function created.

Execute the function.

SQL> VARIABLE rc REFCURSOR
SQL> EXECUTE :rc := dept_fn
PL/SQL procedure successfully completed.

Now print the bind variable.

SQL> PRINT rc

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
4 rows selected.

The function can be executed multiple times using the same or a different REFCURSOR bind variable.

SQL> EXECUTE :rc := dept_fn
PL/SQL procedure successfully completed.

SQL> PRINT rc

    DEPTNO DNAME          LOC  
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.