Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, zhu.

Asked: January 07, 2014 - 11:37 am UTC

Last updated: July 26, 2019 - 3:25 am UTC


Viewed 10K+ times! This question is

You Asked

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);
SQL> select * from test;

       ID1        ID2
---------- ----------
         1          2
         2          1
         2          3

set serveroutput on
  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;
  v_cur := dbms_sql.open_cursor;
  v_sql := 'select count(*)
            from   (select *
                    from   test
                    where  id1=:MYTEST
                    or     id2=:MYTEST)';
  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_output.put_line('count is '||to_char(v_count));
count is 2

PL/SQL procedure successfully completed.

and Tom said...

Execute immediate always uses "positional notation". It binds by ordinal position in the sql statement - regardless of the bind name.


Specifies bind variables, using positional notation.

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      ;
 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

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.

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';
 12      dbms_sql.parse(l_cur,l_sql,dbms_sql.native);
 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);
 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);
 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.


  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment


thanks a lot

zhu, January 08, 2014 - 12:52 am UTC

I see,your explanation is so clear.
Thanks a lot for your kindly help

Best Regards

Binds used in a query

B C, July 25, 2019 - 6:00 pm UTC

Is it possible to determine what bind variables are used by a query ? For example, If I were to execute the below

dbms_sql.parse(l_i_cursor_id, 'select table_name, num_rows from all_tables where owner = :p_owner', dbms_sql.native);

After parsing, does dbms_sql know that :p_owner requires a bind and save this information somewhere where I can retrieve it ?

Would that be store in a v$sql_bind_capture ?


Connor McDonald
July 26, 2019 - 3:25 am UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library