Hi, Tom
I wrote the following procedure, there are two PLACEHOLDERs which means two bind variables
and the two PLACEHOLERS have the same name, that is to say ,the name of PLACEHOLDERs are identical,
they are both "MYTEST".
In the procedure, I assign a value to the PLACEHOLDERs by DBMS_SQL.BIND_VARIABLE only once,
but if I use 'EXECUTE IMMEDIATE' to execute a dynamic SQL, we have to separately assign value
for the TWO PLACEHOLDERs even though their names are same.
I'd like to know if the usage of "two PLACEHOLDERs with same name, bind only once" of DBMS_SQL is correct?
I have done many experiments of this usage and I got the correct result.
Thank a lot
Best Regards
create table test(id1 number,id2 number);
insert into test values(1,2);
insert into test values(2,1);
insert into test values(2,3);
commit;
SQL> select * from test;
ID1 ID2
---------- ----------
1 2
2 1
2 3
set serveroutput on
declare
v_sql varchar2(32767);
v_cur pls_integer;
v_execute pls_integer;
v_column pls_integer;
v_count pls_integer;
V_ID pls_integer := 1;
begin
v_cur := dbms_sql.open_cursor;
v_sql := 'select count(*)
from (select *
from test
where id1=:MYTEST
or id2=:MYTEST)';
dbms_sql.parse(v_cur,v_sql,dbms_sql.native);
dbms_sql.bind_variable(v_cur,'MYTEST',V_ID);
dbms_sql.define_column(v_cur,1,v_column);
v_execute := dbms_sql.execute(v_cur);
if dbms_sql.fetch_rows(v_cur) > 0 then
dbms_sql.column_value(v_cur, 1, v_count);
end if;
dbms_sql.close_cursor(v_cur);
dbms_output.put_line('count is '||to_char(v_count));
end;
/
count is 2
PL/SQL procedure successfully completed.
Execute immediate always uses "positional notation". It binds by ordinal position in the sql statement - regardless of the bind name.
<quote>
...
using_clause
Specifies bind variables, using positional notation.
</quote>
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#LNPLS1224 So, for example:
ops$tkyte%ORA11GR2> declare
2 l_n1 number;
3 l_n2 number;
4 l_n3 number;
5 begin
6 execute immediate 'select :x, :x, :x from dual'
7 into l_n1, l_n2, l_n3
8 using 1, 2, 3
9 ;
10
11 dbms_output.put_line( l_n1 || ', ' || l_n2 || ', ' || l_n3 );
12 end;
13 /
1, 2, 3
PL/SQL procedure successfully completed.
even though I used :x three times, it is as if I used:
select :1, :2, :3 from dual
DBMS_SQL on the other hand, binds by name
<quote>
This procedures binds a given value or set of values to a given variable in a cursor, based on the name of the variable in the statement.
</quote>
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sql.htm#ARPLS68242 ops$tkyte%ORA11GR2> declare
2 l_sql varchar2(32767);
3 l_cur pls_integer;
4 l_execute pls_integer;
5 l_n1 number;
6 l_n2 number;
7 l_n3 number;
8 begin
9 l_cur := dbms_sql.open_cursor;
10 l_sql := 'select :x, :x, :x from dual';
11
12 dbms_sql.parse(l_cur,l_sql,dbms_sql.native);
13
14 dbms_sql.bind_variable(l_cur,'X',1);
15 dbms_sql.bind_variable(l_cur,'X',2);
16 dbms_sql.bind_variable(l_cur,'X',3);
17
18 dbms_sql.define_column(l_cur,1,l_n1);
19 dbms_sql.define_column(l_cur,2,l_n1);
20 dbms_sql.define_column(l_cur,3,l_n1);
21
22 l_execute := dbms_sql.execute(l_cur);
23 if dbms_sql.fetch_rows(l_cur) > 0
24 then
25 dbms_sql.column_value(l_cur, 1, l_n1);
26 dbms_sql.column_value(l_cur, 2, l_n2);
27 dbms_sql.column_value(l_cur, 3, l_n3);
28 dbms_output.put_line( l_n1 || ', ' || l_n2 || ', ' || l_n3 );
29 else
30 dbms_output.put_line( 'this should not happen...' );
31 end if;
32 dbms_sql.close_cursor(l_cur);
33 end;
34 /
3, 3, 3
PL/SQL procedure successfully completed.
as you can see - the last supplied "bind by name" value was used for all three binds.
So, in short - execute immediate is "by position" and dbms_sql is "by name"
If you want to bind by name, but have the ease of native dynamic sql for fetching, or to bind by position but use the procedural access of dbms_sql - you can use to_refcursor/to_cursor API calls in dbms_sql to flip/flop between the two approaches midway.