Oracle – Display Exact Query in Output of Stored Procedure Using Dynamic SQL

dynamic-sqloracleoracle-11g-r2plsqlstored-procedures

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

Best Answer

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>

Related Solutions

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

Your procedure is missing two OUT parameters:

PROCEDURE myprocedure (myinput      IN Varchar2, 
                       myretcursor  OUT sys_refcursor,
                       p_count      OUT NUMBER,
                       p_message    OUT VARCHAR2) IS
BEGIN
   OPEN myretcursor FOR
      SELECT * FROM MYTABLE;
   p_count := 123;
   p_message := 'message';
END myprocedure

You would also need to modify your C# code to register the new OUT parameters accordingly.

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

Best Answer

Related Solutions

How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

Related Question