0

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

3
  • 1
    You should output the query before you use it (in case it throws an error). – Michael Kutz Jul 25 '20 at 13:37
  • @MichaelKutz Could you please give me an example? How can I see the query with the input parameter that is passed to the procedure? – Pantea Jul 25 '20 at 13:40
  • 2
    You've misunderstood how Bind variables work. TL;DR spit out the value of you Bind values also. done. – Michael Kutz Jul 25 '20 at 14:28
2

Would REPLACE do any good? See line #6 and result of DBMS_OUTPUT.PUT_LINE.

SQL> 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));    --> like this
  7
  8      execute immediate qry into o_count using i_tx;
  9  end;
 10  /

Procedure created.

SQL> 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               --> result you want, I guess

PL/SQL procedure successfully completed.

SQL>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.