VARIABLE
The VARIABLE command is used to declare bind variables. Bind variables are discussed in Chapter 7. They are real variables that can be used within a PL/SQL block or SQL statement.
VAR[IABLE]var_name
data_type
where:
- VAR[IABLE]
Is the command, which may be abbreviated to VAR.
- var_name
Is whatever name you want to give the variable. A variable name must start with a letter, but after that, the name may contain any combination of letters, digits, underscores, pound signs, and dollar signs. 30 characters is the maximum length for a variable name.
- data_type
Is the datatype of the variable. The following datatypes are allowed:
- NUMBER
This 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) would not be 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
Get Oracle SQL*Plus: The Definitive Guide now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.