I’ve troubleshooted one query today and I was very surprised that bind variables in this query were specified with &ersand instead of :colon! I have never seen this before and I couldn’t find anything about this in documentation…
Unfortunately SQL*Plus doesn’t support ampersand yet, even if you disable define (“set define off”),
so I’ve tested such behaviour with this code:
set def off serverout on exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
And it really works! //at least on 11.2.0.2 and 12.2.0.1
SQL> set def off serverout on
SQL> exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
1
PL/SQL procedure successfully completed.
SQL> select substr(sql_text,1,40) stext,sql_id,executions,rows_processed from v$sqlarea a where sql_text like '%dual%&var';
STEXT SQL_ID EXECUTIONS ROWS_PROCESSED
------------------------------------- ------------- ---------- --------------
select 1 from dual where dummy=&var ckkw4u3atxz02 3 3
SQL> select * from table(dbms_xplan.display_cursor('ckkw4u3atxz02'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID ckkw4u3atxz02, child number 0
-------------------------------------
select 1 from dual where dummy=&var
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DUMMY"=:VAR)
18 rows selected.
Update: Btw, it works for SQL only, not for PL/SQL:
SQL> var v varchar2(1);
SQL> begin &v = 'Z'; end;
2 /
begin &v = 'Z'; end;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "&" when expecting one of the following:
SQL> exec &v := 'X';
BEGIN &v := 'X'; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "&" when expecting one of the following:
The symbol "&" was ignored.
SQL> exec :v := 'X'; PL/SQL procedure successfully completed. SQL> select * from dual where dummy=&v 2 ; D - X
And we can can use mixed placeholders:
SQL> select * from dual where dummy=&v and &v=:v; D - X