Using Bind Variables

Suppose that you want to be able to display the variables you use in your PL/SQL subprograms in SQL*Plus or use the same variables in multiple subprograms. If you declare a variable in a PL/SQL subprogram, you cannot display that variable in SQL*Plus. Use a bind variable in PL/SQL to access the variable from SQL*Plus.

Bind variables are variables you create in SQL*Plus and then reference in PL/SQL or SQL. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. You can use bind variables for such things as storing return codes or debugging your PL/SQL subprograms.

Because bind variables are recognized by SQL*Plus, you can display their values in SQL*Plus or reference them in PL/SQL subprograms that you run in SQL*Plus.

Creating Bind Variables

You create bind variables in SQL*Plus with the VARIABLE command. For example

VARIABLE ret_val NUMBER

This command creates a bind variable named ret_val with a datatype of NUMBER. For more information, see the VARIABLE command in Chapter 8. (To list all bind variables created in a session, type VARIABLE without any arguments.)

Referencing Bind Variables

You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example

:ret_val := 1;

To change this bind variable in SQL*Plus, you must enter a PL/SQL block. For example:

VARIABLE ret_val NUMBER
BEGIN
 :ret_val:=4;
END;
/


PL/SQL procedure successfully completed.

This command assigns a value to the bind variable named ret_val.

Displaying Bind Variables

To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example:

PRINT RET_VAL


   RET_VAL
----------
         4

This command displays a bind variable named ret_val. For more information about displaying bind variables, see the PRINT command in the "Command Reference" in Chapter 8.

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.

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-17 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

VARIABLE employee_info 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 employee_info bind variable to the cursor variable.

BEGIN

 OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY 

 FROM EMP_DETAILS_VIEW

 WHERE JOB_ID='SA_MAN' ;
END;
 /


PL/SQL procedure successfully completed.

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

PRINT employee_info


EMPLOYEE_ID     SALARY
----------- ----------
        145      14000
        146      13500
        147      12000
        148      11000
        149      10500

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

Example 3-18 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.

CREATE OR REPLACE PACKAGE cv_types AS
TYPE EmpInfoTyp is REF CURSOR RETURN emp%ROWTYPE;
END cv_types;/


Package created.

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

CREATE OR REPLACE PROCEDURE EmpInfo_rpt
(emp_cv IN OUT cv_types.EmpInfoTyp) AS
BEGIN
OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW -
WHERE JOB_ID='SA_MAN' ;
 END;
 /


Procedure created.

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

VARIABLE odcv REFCURSOR
EXECUTE EmpInfo_rpt(:odcv)


PL/SQL procedure successfully completed.

Now print the bind variable.

PRINT odcv


EMPLOYEE_ID     SALARY
----------- ----------
        145      14000
        146      13500
        147      12000
        148      11000
        149      10500

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

VARIABLE pcv REFCURSOR
EXECUTE EmpInfo_rpt(:pcv)


PL/SQL procedure successfully completed.


PRINT pcv


EMPLOYEE_ID     SALARY
----------- ----------
        145      14000
        146      13500
        147      12000
        148      11000
        149      10500

Example 3-19 Using REFCURSOR Variables in Stored Functions

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

CREATE OR REPLACE FUNCTION EmpInfo_fn RETURN -
cv_types.EmpInfo IS
resultset cv_types.EmpInfoTyp;
BEGIN
OPEN resultset FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW -
WHERE JOB_ID='SA_MAN' ;
RETURN(resultset);
END;
 /


Function created.

Execute the function.

VARIABLE rc REFCURSOR
EXECUTE :rc := EmpInfo_fn


PL/SQL procedure successfully completed.

Now print the bind variable.

PRINT rc


EMPLOYEE_ID     SALARY
----------- ----------
        145      14000
        146      13500
        147      12000
        148      11000
        149      10500

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

EXECUTE :rc := EmpInfo_fn


PL/SQL procedure successfully completed.


PRINT rc


EMPLOYEE_ID     SALARY
----------- ----------
        145      14000
        146      13500
        147      12000
        148      11000
        149      10500