What is the difference between bind variables and substitution variables(which I input using &&)?

Question

What is the difference between these two variable declarations?

1: num number:='&&num';
2: variable num1 number;

Since in both cases I can reference num by using &num or &&num in other files also, and in the case of bind variables :num1.

Moreover I have one more confusion: whether any of the below statements differ somehow, are they both valid and do they mean the same thing?

1: variable num1 number;
2: var num1 number;

Answer 1

You appear to have some confusion about the differences between bind variables in Oracle and substitution variables in SQL*Plus.

Let's start with substitution variables. Substitution variables are unique to SQL*Plus and are not part of the database. They won't work if you try to use them with JDBC, for example.

Substitution variables can only hold a piece of text. If SQL*Plus encounters a substitution variable in a line of input, it will replace the variable with its text contents:

SQL> define subvar=X
SQL> select * from dual where dummy = &subvar;
old   1: select * from dual where dummy = &subvar
new   1: select * from dual where dummy = X
select * from dual where dummy = X
                                 *
ERROR at line 1:
ORA-00904: "X": invalid identifier

Note that SQL*Plus replaced our substitution variable with its text value with no regard for whether it gave us valid SQL. In the example above, we omitted the single quotes around &subvar and it gave us invalid SQL, so we got an error.

The lines beginning old and new show us the line we entered before and after SQL*Plus applied the substitution variables. The new line is the line the database tried to run.

You can enable or disable the display of the old and new lines using SET VERIFY ON and SET VERIFY OFF. You can also turn the replacement of substitution variables on or off by using SET DEFINE ON and SET DEFINE OFF.

If we want to run the above query using the substitution variable, we must put quotes around it:

SQL> select * from dual where dummy = '&subvar';
old   1: select * from dual where dummy = '&subvar'
new   1: select * from dual where dummy = 'X'

D
-
X

If &subvar happens to contain a string that was a valid number (e.g. 5), then we can get away without using the quotes, but that's only because taking out the text &subvar and replacing it with the text 5 happens to give us valid SQL.

For example, suppose we have a table called test with the following data in it:

         A
----------
         1
         2
         3
         4
         5

Then we can do

SQL> define subvar=5
SQL> select * from test where a = &subvar;
old   1: select * from test where a = &subvar
new   1: select * from test where a = 5

         A
----------
         5

Bind variables, on the other hand, have types. They are not simple text values. Their values are sent to the database, and the database can also set their values.

SQL> variable bindvar varchar2(1);
SQL> exec :bindvar := 'X';

PL/SQL procedure successfully completed.

You don't put quotes around a bind variable when you want to use it:

SQL> select * from dual where dummy = :bindvar;

D
-
X

SQL> select * from dual where dummy = ':bindvar';

no rows selected

In the second example above, we got no rows returned because the DUAL table has no rows with the DUMMY column containing the text :bindvar.

You'll get an error if you attempt to assign a value of the wrong type to a bind variable:

SQL> variable bindvar number;
SQL> exec :bindvar := 'X';
BEGIN :bindvar := 'X'; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1

Bind variables are a standard part of the database, and you can use them with JDBC or whichever method of connecting to the database you choose.

---

Finally, variable num1 number and var num1 number both mean the same thing. They both define a bind variable num1 of type number. var is just an abbreviation for variable.