Using REFCURSOR Bind Variables

SQL*Plus REFCURSOR bind variables allow SQL*Plus to fetch and format the results of a SELECT statement contained in a PL/SQL block.

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.

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