Oracle SQL Plus Pocket Reference, 2nd Edition

Name

VARIABLE

Synopsis

VAR[IABLE] [variable_name [data_type]]

The VARIABLE command declares bind variables. For example:

VARIABLE x NUMBER
VARIABLE query_results REFCURSOR
VARIABLE emp_name VARCHAR2(40)

Bind variables are real variables that can be used within a PL/SQL block or SQL statement. Issuing the VARIABLE command with no parameters generates a list of all currently defined bind variables.

Parameters

variable_name

The name you want to give the variable. If you stop here and don’t supply a datatype, SQL*Plus displays the datatype for the variable that you have named.

data_type

The datatype of the variable. The following datatypes are allowed:

NUMBER

Results in a floating-point number and is the same as a NUMBER variable in PL/SQL or a NUMBER column in a table. Unlike PL/SQL, SQL*Plus does not let you specify a length or a precision, so a declaration like NUMBER (9,2) is not allowed.

CHAR [(length [CHAR | BYTE])]

Results in a fixed-length character string. The length is optional. If it’s omitted, you get a one-byte string.

NCHAR [(length)]

Results in a fixed-length character string in the national character set. The length is optional. If it’s omitted, you get a one-character string.

VARCHAR2 (length [CHAR | BYTE])

Results in a variable-length character string.

NVARCHAR2 (length)

Results in a variable-length character string using the national language character set.

CLOB

Results in a character large object variable.

NCLOB

Results in a character large object variable using the national language ...