Return to Contents

Next Page

3 Using Bind Variables

Bind variables are used in SQL and PL/SQL statements for holding data or result sets. They are commonly used in SQL statements to optimize statement performance. A statement with a bind variable may be re-executed multiple times without needing to be re-parsed. Their values can be set and referenced in PL/SQL blocks. They can be referenced in SQL statements e.g. SELECT. Except in the VARIABLE and PRINT commands, bind variable references should be prefixed with a colon.

Bind variables are created with the VARIABLE command. The following PL/SQL block sets a bind variable:

    SQL> variable bv number
    SQL> begin
      2    :bv := 8;
      3  end;
      4  /


    PL/SQL procedure successfully completed.

Once a value is set, you can show it with the PRINT command.

    SQL> print bv

            BV

    ----------
             8

Numeric bind variables can be used in the EXIT command to return a value to the operating system:

    SQL> EXIT :bv

Other SQL*Plus commands do not recognize bind variables.

There is no way to undefine or delete a bind variable in a SQL*Plus session. However, bind variables are not remembered when you exit SQL*Plus.

For information about automatically displaying values and using REFCURSOR bind variables for whole result sets, see Using Bind Variables and VARIABLE, in the SQL*Plus User's Guide and Reference.

3.1 Assigning Substitution Variables to Bind Variables

You can assign a substitution variable to a bind variable:

    SQL> define mysubv = 123
    SQL> variable mybndv number
    SQL> execute :mybndv := &mysubv;

SQL*Plus executes the PL/SQL assignment statement after it substitutes the value of "mysubv". If "mysubv" was not already defined, you would be prompted for a value.

The bind variable can be used in subsequent SQL or PL/SQL commands.

3.2 Assigning Bind Variables to Substitution Variables

Sometimes it is useful to make the value of a bind variable available to SQL*Plus commands like TTITLE or SPOOL. For example, you might want to call a PL/SQL function that returns a string and use the value for a SQL*Plus spool file name. The SPOOL command does not understand bind variable syntax so the bind variable value needs to be assigned to a substitution variable first.

This is done using COLUMN NEW_VALUE and SELECT commands. For example, declare a bind variable in SQL*Plus and instantiate it in a PL/SQL block. Its value can be returned from a PL/SQL function, or like here, set by a direct assignment:

    SQL> variable mybv varchar2(14)
    SQL> begin
      2    /* ... */
      3    :mybv := 'report.log';
      4  end;
      5  /

Pass the bind variable's value to a new substitution variable "nv" by using a query:

    SQL> column mybvcol new_value nv noprint
    SQL> select :mybv mybvcol from dual;

Now you can use the substitution variable in a SPOOL command:

    SQL> spool &nv

The SPOOL command executes as if you had typed

    SQL> spool report.log

Return to Contents

Next Page