O'Reilly logo

Oracle SQL*Plus: The Definitive Guide, 2nd Edition by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Bind Variables

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.

Example 11-1. Bind variables can be used to transfer data among PL/SQL blocks and SQL queries
--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.

Declaring Bind Variables ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required