advertisements
_____________________________________________________________________________________________________________________
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_____________________________________________________________________________________________________________________
0 comments:
Post a Comment