Oracle Variables

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

:MyVar := 15000

DECLARE

:=
Prefix & : none
How to Examine DEFINE MyVar PRINT MyVar DBMS_OUTPUT.put_line
or assign to a bind variable
or just 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.


 
Copyright © 1999-2024 SS64.com
Some rights reserved