|
|
Oracle allows variables to 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.
Parameters
Pass values to a SQL Plus script with the START command
e.g.
START MySQLScript.sql Finance 15000
Within the script &1 will now evaluate as 'finance' and &2
as 15000
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.
