Name
VARIABLE
Synopsis
VAR[IABLE] [variable_name [data_type]]
The VARIABLE command is used to declare 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
Is 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
Is 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)]
Results in a fixed-length character string. Length is optional. If it’s omitted, you get a one-character string.
- NCHAR [(length)]
Results in a fixed-length character string in the national character set. Length is optional. If it’s omitted, you get a one-character string.
- VARCHAR2 (length)
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 character set.
- REFCURSOR
Gives you a cursor variable you can use to return the results of a SQL query from PL/SQL to SQL*Plus.