SQLPLUS - Variable

SQLPLUS can manage two types of variable:

• substitution variable in SQL

The substitution variables can have two scopes: temporary and permanent.

• bind variable in PL/SQL

You can define variables, called substitution variables, for repeated use in a single script. Note that you can also define substitution variables to use in titles and to save your keystrokes (by defining a long string as the value for a variable with a short name).

A substitution variable is preceded by one or two ampersands (&).

When SQLPlus find a substitution variable define by using only one ampersand (&),

• it tries to replace it with the value of one permanent substitution variable previously defined
• otherwise, it will prompt you to enter a value that will be use only once.

A permanent Substitution Variables is a variable available for the complete session.

To define a substitution variable, you can use:

• the DEFINE command
• two Ampersands
• the ACCEPT command
• the COLUMN NEW_VALUE

DEFINE L_NAME = SMITH

Note that any substitution variable you define explicitly through DEFINE takes only CHAR values (that is, the value you assign to the variable is always treated as a CHAR datatype). You can define a substitution variable of datatype NUMBER implicitly through the ACCEPT command.

SQL*Plus automatically DEFINEs any substitution variable preceded by two ampersands, but does not DEFINE those preceded by only one ampersand.

gerardnico@orcl>select &&MySubstitutionVariable FROM dual;
Enter VALUE FOR mysubstitutionvariable: 'Value'
old   1: SELECT &&MySubstitutionVariable FROM dual
NEW   1: SELECT 'Value' FROM dual
 
'VALUE'
-------
VALUE
 
gerardnico@orcl>DEFINE MySubstitutionVariable
DEFINE MYSUBSTITUTIONVARIABLE = "'Value'" (CHAR)

ACCEPT pswd CHAR PROMPT 'Password:  ' HIDE

or for a number

ACCEPT salary NUMBER FORMAT '999.99' DEFAULT '000.0' -
PROMPT 'Enter weekly salary:  '
PROMPT You have enter: &salary

You can store the value of a column value in a variable using this statement

COLUMN column_name NEW_VALUE variable_name

Example:

define SpoolFileId=idle
column ReleaseId NEW_VALUE SpoolFileId 
select TO_CHAR(SYSDATE,'YYYYMMDD_HH24MMSS_') || '_Release.log' ReleaseId from dual; 
PROMPT The value of SpoolFileId is now: &SpoolFileId

SPOOL '&SpoolFileId'

To delete a substitution variable, use the SQL*Plus command UNDEFINE followed by the variable name.

UNDEFINE MySubstitutionVariable

To list all substitution variable definitions, enter DEFINE by itself.

SQL> DEFINE
DEFINE _DATE           = "14-JUN-10" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "bidb" (CHAR)
DEFINE _USER           = "DWH" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.
4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (
CHAR)
DEFINE _O_RELEASE      = "1002000400" (CHAR)
DEFINE MY_SUBSTITUTION_VARIABLE       = "1" (CHAR)

PROMPT The value of MySubstitutionVariable is: &MySubstitutionVariable

or

DEFINE MySubstitutionVariable

SET DEFINE defines the substitution character (by default the ampersand ”&”) and turns substitution on and off.

After you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. You can suppress this listing by setting the SET command variable VERIFY to OFF.

SET VERIFY ON. Lists each line of the script before and after substitution.

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 variable 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.

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. See the VARIABLE command for more information. (To list all bind variables created in a session, type VARIABLE without any arguments.)

You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example To change this bind variable in SQL*Plus, you must enter a PL/SQL block. For example:

BEGIN
 :ret_val:=4;
END;
/
 
PL/SQL PROCEDURE successfully completed.

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

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

• The ACCEPT command
• Substitution Variable Examples