How to Disable / Escape / Avoid / Stop & and && Variable Substitutions in Oracle SQL Plus


Difference between & and &&
‘&’ is used to substitute values for one time to the variable. So whenever you execute the sql with & it will prompt for the variable values.


‘&&’ is used to substitute the values permanently for the variable and the variable value is valid until the session close. Only once you have to enter the value and it will be applicable forever until session exit.

Examples:

Example for & - Each time ask for the parameter value
SQL> select ename, job, sal from emp where empno=&eno;
Enter value for eno: 7369
old   1: select ename, job, sal from emp where empno=&eno
new   1: select ename, job, sal from emp where empno=7369

ENAME      JOB              SAL
---------- --------- ----------
smith      clerk            800

SQL> /
Enter value for eno: 7902
old   1: select ename, job, sal from emp where empno=&eno
new   1: select ename, job, sal from emp where empno=7902

ENAME      JOB              SAL
---------- --------- ----------
ford       analyst         3000

Example for && - It ask for only once and the query execute with same value in subsequent executions.

SQL> select ename, job, sal from emp where empno=&&employeeno;
Enter value for employeeno: 7782
old   1: select ename, job, sal from emp where empno=&&employeeno
new   1: select ename, job, sal from emp where empno=7782

ENAME      JOB              SAL
---------- --------- ----------
clark      manager         2450

SQL> /
old   1: select ename, job, sal from emp where empno=&&employeeno
new   1: select ename, job, sal from emp where empno=7782

ENAME      JOB              SAL
---------- --------- ----------
clark      manager         2450

How to Refresh / Renew the value for && variable?
You can use undefine command to reset the value for the && variables. So next time it will ask for new value.

SQL> undefine employeeno;
SQL> select ename, job, sal from emp where empno=&&employeeno;
Enter value for employeeno: 7369
old   1: select ename, job, sal from emp where empno=&&employeeno
new   1: select ename, job, sal from emp where empno=7369

ENAME      JOB              SAL
---------- --------- ----------
smith      clerk            800

How to Escape or Avoid & in SQLs and PLSQL?

If you have ‘&’ character in your SQL or PLSQL it will prompt for the parameter value during the execution or compilation of PLSQL code.

See one example here. I am just selecting AT&T from dual which is prompting for the input.
SQL> select 'AT&T' from dual;
Enter value for t: 4
old   1: select 'AT&T' from dual
new   1: select 'AT4' from dual

'AT
---
AT4

There are 3 ways to escape this.
Method 1:  With escape
SQL> set escape \
SQL>  select 'AT\&T' from dual;

'AT&
----
AT&T

Method 2: with define

SQL> set escape off
SQL> select 'AT&T' from dual
SQL> /
Enter value for t: 4
old   1:  select 'AT&T' from dual
new   1:  select 'AT4' from dual

'AT
----
AT4

SQL> set define off
SQL> select 'AT&T' from dual
SQL> /

'AT&
-----
AT&T

SQL> select 'AT&T' from dual;

'AT&
----
AT&T

Method 3: Using chr function
SQL> SELECT 'AT'||chr(38)||'T' from dual;

'AT'
----
AT&T
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google