1

Topic: Bind variables: and instead of:

Something I now faced for the first time: in the request text (v$sqlarea.sql_text/sql_fulltext) bind variables are specified through and, for example:

select * from dual where x=&var

And it really works!
In the plan thus predicates shows as x =:var.
When the such appeared? sqlplus it is exact does not support - I checked up. Only some the driver support it?
Request this of , which  DOA

2

Re: Bind variables: and instead of:

And the developer states that in most  it is request is written through ":", but the driver so transforms it...

3

Re: Bind variables: and instead of:

At us colons are accurately visible
& <= this  never saw
I look normally v$sql_text_with_new_lines

4

Re: Bind variables: and instead of:

Also it is unique such request in basis now

5

Re: Bind variables: and instead of:

SQL> set def off
SQL> set serverout on
SQL> declare s varchar2 (1); begin execute immediate ' select 1 from dual where dummy=&var ' into s using ' X '; dbms_output.put_line (s); end;
2 /
1
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

6

Re: Bind variables: and instead of:

And on 11.2.0.2 the same:

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 * from v$version;
BANNER
-------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
5 rows selected.

Test case:

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;

7

Re: Bind variables: and instead of:

xtender;
11.2.0.4 supports.
In dock it is laconically told
[quote =] If the dynamic SQL statement includes placeholders for bind variables, each placeholder must have a corresponding bind variable in the appropriate clause of the EXECUTE IMMEDIATE statement, as follows
* If the dynamic SQL statement is a SELECT statement that can return at most one row, put out-bind variables ( defines ) in the INTO clause and in-bind variables in the USING clause.