'&' vs '&&' in ORACLE

“&” is used to create a temporary substitution variable. You will be prompted to enter the value every time the variable is referenced.

“&&” is used to create a permanent substitution variable. You need to enter the value only once.

1. Using ‘&’

SELECT employee_name FROM employee

    WHERE emp_id ='&emp'

Every time, you execute this select statement, you will have to pass the value of emp.

2. Using ‘&&’

Consider the following procedure.

SQL> set serveroutput on;

SQL> CREATE OR REPLACE PROCEDURE test_substitution AS

   emp_name VARCHAR2(10);

   emp_designation VARCHAR2(10);

   BEGIN

   SELECT employee_name INTO emp_name FROM employee

   WHERE emp_id ='&&emp';

   dbms_output.put_line(emp_name);

   SELECT employee_desg INTO emp_designation FROM employee

   WHERE emp_id='&emp';

   dbms_output.put_line(emp_designation);

   END;

 /

Procedure created

This procedure fetches the employee_name and employee_desg from the employee table for a particular emp_id.

It will prompt for the emp_id only once as ‘&emp’ is used. In the next select statement,although a substitution variable is used, you will not be prompted to enter a value for emp.

The next time you execute the procedure,it will not prompt for the value to be passed.

If you need to pass a different value each time you run the procedure, then you will have to undefine the variable emp.

SQL> undefine emp;

AND then compile the procedure again. This will prompt for a new value to be passed.

If you just undefine the variable and execute the procedure, it will not prompt for a new value.

It is important to undefine and then recompile the procedure.