Sql*Plus User’s Guide and Reference

5-22
SQL*Plus User's Guide and Reference
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:
BEGIN
:ret_val:=4;
END;
/
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
This command displays a bind variable named ret_val. See 
PRINT
 on page 12-71 for 
more information about displaying bind variables.
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 enables 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 5–14
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
create procedure p4 as
c1 sys_refcursor;
c2 sys_refcursor;
begin
open c1 for SELECT * FROM DEPT;
dbms_sql.return_result(c1);
open c2 for SELECT * FROM EMP;
dbms_sql.return_result(c2);
end;
/
PL/SQL procedure successfully completed.
RET_VAL
----------
4

---

Using REFCURSOR Bind Variables
Using Scripts in SQL*Plus
5-23
create procedure p4 as
c1 sys_refcursor;
c2 sys_refcursor;
begin
open c1 for SELECT * FROM DEPT;
dbms_sql.return_result(c1);
open c2 for SELECT * FROM EMP;
dbms_sql.return_result(c2);
end;
/
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 Oracle 
Database PL/SQL Language 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;
/
The results from the SELECT statement can now be displayed in SQL*Plus with the 
PRINT command.
PRINT employee_info
The PRINT statement also closes the cursor. To reprint the results, the PL/SQL block 
must be executed again before using PRINT.