Defining variables Oracle script

Question

I'm trying to use variables in a Oracle SQL script. Following the How to declare variable and use it in the same SQL script? (Oracle SQL) post I have defined my variables as follows:

DEFINE dummyvar INT := 1;
SELECT '&dummyvar' FROM DUAL;

This leads however to popup's asking for a value when executing the script (when reusing a variable it keeps generating popups asking for a value). Is it possible to prevent the generation of popups when a value for a variable is defined? so in the example above I want to prevent the generation of a popup; when a variable is defined as:

DEFINE dummyvar INT;
SELECT '&dummyvar' FROM DUAL;

a popup may be generated to enter the popup.

This for the execution of the script in SQL Developer.

Thanks.

If this is specifically about SQL Developer (or PL/SQL Developer, or Toad, or something else), please confirm and tag the question. It's not clear what kind of "Oracle script" this is. — William Robertson, Nov 29 '17 at 17:47

Kaushik Nayak · Answer 1 · 2017-11-29T15:46:52.433

You can use VAR instead of define if you need specific datatype. INT is not valid in VAR and only valid in SQL, PL/SQL.

This works in SQL developer using RUN script (F5).

VAR dummyvar NUMBER;
EXEC :dummyvar := 1;
SELECT :dummyvar value FROM DUAL;

This works on SQL* Plus.Note the correct syntax for DEFINE. You were using := instead of =. Also, enclosing it within quotes would cause it to ignore the value.

DEFINE dummyvar  = 1;
SELECT &dummyvar FROM DUAL;

score 0 · Accepted Answer · edited Nov 29 '17 at 17:51

Whenever you run a stored query or script, SQL*Plus substitutes the value of variable for each substitution variable referencing variable (in the form &variable or &&variable). SQL*Plus will not prompt you for the value of variable in this session until you UNDEFINE variable.

If the value of a defined variable extends over multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return with a space. For example, SQL*Plus interprets

DEFINE TEXT = 'ONE-
TWO-
THREE'

as

DEFINE TEXT = 'ONE TWO THREE'

You should avoid defining variables with names that may be identical to values that you will pass to them, as unexpected results can occur. If a value supplied for a defined variable matches a variable name, then the contents of the matching variable are used instead of the supplied value.

Some variables are predefined when SQL*Plus starts. Enter DEFINE to see their definitions.

Examples

To assign the value MANAGER to the variable POS, type:

DEFINE POS = MANAGER

If you execute a command containing a reference to &POS, SQL*Plus substitutes the value MANAGER for &POS and will not prompt you for a POS value.

To assign the CHAR value 20 to the variable DEPARTMENT_ID, type:

DEFINE DEPARTMENT_ID = 20

Even though you enter the number 20, SQL*Plus assigns a CHAR value to DEPARTMENT_ID consisting of two characters, 2 and 0.

To list the definition of DEPARTMENT_ID, enter

DEFINE DEPARTMENT_ID
DEFINE DEPARTMENT_ID = "20" (CHAR)

This result shows that the value of DEPARTMENT_ID is 20.

Defining variables Oracle script

2 Answers2