'&' 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 ename FROM emp

    WHERE empno ='&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

   v_ename VARCHAR2(10);

   v_job VARCHAR2(10);

   BEGIN

   SELECT ename INTO v_ename FROM emp

   WHERE empno ='&&emp';

   dbms_output.put_line(v_ename);

   SELECT job INTO v_job FROM emp

   WHERE empno='&emp';

   dbms_output.put_line(v_job);

   END;

 /

Procedure created

This procedure fetches the employee name(ename) and employee designation(job)  from the emp table for a particular empno.

It will prompt for the empno 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.