Back in Chapter 8, you learned about substitution variables. SQL*Plus supports another type of variable called a bind variable. Unlike substitution variables, bind variables are real variables, having a datatype and a size.
Bind variables were created to support the use of PL/SQL in a SQL*Plus script. They provide a mechanism for returning data from a PL/SQL block back to SQL*Plus, where that data can be used in subsequent queries or by other PL/SQL blocks. Example 11-1 provides a simple script showing how a bind variable can be used.
--Bind variables can be declared in your SQL*Plus script. VARIABLE s_table_name varchar2(30) --Preface a bind variable with a colon to reference it --in a PL/SQL block. BEGIN :s_table_name := 'EMPLOYEE'; END; / --Bind variables can even be referenced by SQL queries. SELECT index_name FROM user_indexes WHERE table_name = :s_table_name; --Bind variables persist until you exit SQL*Plus, so --they can be referenced by more than one PL/SQL block. SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE(:s_table_name); END; /
The scope of a bind variable is the SQL*Plus session in which it is defined. Variables defined within a PL/SQL block, on the other hand, cease to exist once that block has finished executing. Bind variables are defined one level higher (at the SQL*Plus level), so they can be referenced by many PL/SQL blocks and queries.