Variables can be defined in several places:
| Substitution SQL*Plus |
Host/Bind SQL*Plus or other Host Program |
PL/SQL Stored Procedure |
|
| Where to use | For passing values into an SQL script. | In SQL Plus and in PL/SQL blocks | Used only within a PL/SQL block |
| How to initialise and assign | DEFINE MyVar=15000 ACCEPT MyVar PROMPT Enter Var. && MyVar & MyVar |
VARIABLE MyVar NUMBER |
DECLARE := |
| Prefix | & | : | none |
| How to Examine | DEFINE MyVar | PRINT MyVar | DBMS_OUTPUT.put_line or assign to a bind variable or simply write values into a table. |
To avoid being re-prompted for substitution variables use a double ampersand - this will DEFINE the variable and you won't be re-prompted when Sql*Plus encounters the same value again (within the same session.)
SET CONCAT - This defines the concatenation character (default= . ) For characters that immediately follow the variable.
Example - Passing values to a SQL Plus script with the START (or @) command:
SQL> @MyScript.sql Finance 15
Within the script &1 will now evaluate as finance and &2 as 15
SQL> @MyScript.sql Sales 25
This time the same script will run and substitute &1 as sales and &2 as 25
Related commands
If you are using Java stored procedures then you can add Java variables to the
above.
defined as:
datatype variable_name;
where datatype is: string, char, boolean, byte, short, int, long, float,
double, or a class variable.