What is the difference between bind variables and the variable I entered with &&?

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 using &num or &&num in other files, and in the case of bind variables :num1 .

In addition, I have one more confusion: does any of the following statements differ in any way, are they valid and do they mean the same thing?

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

Answer 1

You seem to be confused about the differences between the bind variables in Oracle and the substitution variables in SQL * Plus.

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

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

  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

Please note that SQL * Plus replaced our substitution variable with its text value, regardless of whether it gave us valid SQL. In the above example, we omitted the single quotes around &subvar and this gave us invalid SQL, so we got an error message.

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

You can enable or disable the display of the old and new strings using SET VERIFY ON and SET VERIFY OFF . You can also enable or disable substitution variable substitution using SET DEFINE ON and SET DEFINE OFF .

If we want to execute the above query using a lookup variable, we must put quotation marks there:

  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 contains a string that was a real number (for example, 5 ), we can leave without using quotes, but this is only because taking out the text &subvar and replacing it with text 5 gives us real SQL.

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

  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

Binding variables, on the other hand, have types. These are not simple text values. Their values ​​are sent to the database, and the database can also set its values.

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

 PL / SQL procedure successfully completed.

You do not put quotes around the binding variable if 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 did not get rows, because the DUAL table does not have rows with a DUMMY column containing the text :bindvar .

You will receive an error message if you try to assign the value of the wrong type to the binding 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

Bound variables are a standard part of the database, and you can use them with JDBC or any way to connect to the database of your choice.

---

Finally, variable num1 number and var num1 number mean the same thing. They both define the num1 binding num1 type number . var is an acronym for variable .