How to display the exact query in the output section of a stored procedure when using 'Dynamic SQL' and 'Bind variables' in Oracle I have a Procedure as you can see below. In this procedure I have a dynamic querywhich is qry variable and I'm using Bind variable in order to eliminate the risk of injection and improve performance:
create or replace procedure first_test(i_tx varchar2, o_count out number) is

qry varchar2(1000);
begin
    qry := 'select count(*) from z_test_a where c_num = :1';

    execute immediate qry
    into o_count
    using i_tx;

   dbms_output.put_line(qry);
end;

what I want to know is that , how I can see the exact qry after using i_tx. What I mean is that if the input parameter is i_tx = 2 how I can see this select count(*) from z_test_a where c_num = 2 in the output section of the procedure so that I can debug the code in case of error? Is this even possible to see the exact query? In this procedure , what I'm getting right now in the output page is :select count(*) from z_test_a where c_num = :1.
Thanks in advance
Would REPLACE do any good? See line #6 and result of DBMS_OUTPUT.PUT_LINE.
SQL&gt; create or replace procedure first_test(i_tx varchar2, o_count out number) is
  2    qry varchar2(1000);
  3  begin
  4      qry := 'select count(*) from z_test_a where c_num = :1';
  5
  6      dbms_output.put_line(replace(qry, ':1', i_tx));    --&gt; like this
  7
  8      execute immediate qry into o_count using i_tx;
  9  end;
 10  /

Procedure created.

SQL&gt; declare
  2    l_cnt number;
  3  begin
  4    first_test(1, l_cnt);
  5  end;
  6  /
select count(*) from z_test_a where c_num = 1               --&gt; result you want, I guess

PL/SQL procedure successfully completed.

SQL&gt;