How to retrieve parsed dynamic pl Sql

Question

I have many PL/SQL functions and procedures that execute dynamic sql.

Is it possible to extract the parsed statements and dbms_output as an debugging aid ?

What I really want is to see the parsed sql (sql statement with substituted parameters).

Example:

I have a dynamic SQL statement like this

 SQ:='SELECT  :pComno as COMNO,null t$CPLS,t$CUNO,t$cpgs,t$stdt,t$tdat,t$qanp,t$disc,:cS Source FROM BAAN.TTDSLS031'||PCOMNO --1
    || ' WHERE' ||' TRIM(T$CUNO)=trim(:CUNO)' --2  
    ||    ' AND  TRIM(T$CPGS)=trim(:CPGS)' --3
    ||    ' AND  T$QANP      = priceWorx.fnDefaultQanp ' --4
    ||    ' AND  priceWorx.fdG2J(sysdate) between priceWorx.fdG2J(t$stdt) and priceWorx.fdG2J(t$tdat)' --5
    ||    ' AND  rownum=1 order by t$stdt';--6

    execute immediate SQ into R using 
        PCOMNO,'C' --1
        ,PCUNO-- 2
        ,PCPGS;-- 3

What will be the statement sent to the server ?

You want retrieve the `dbms_output.put_line` of your dynamic sql, is this? — , Mar 07 '12 at 19:13

score 2 · Accepted Answer · answered Mar 08 '12 at 05:32

You can display the bind variables associated with a SQL statement like this:

select v$sql.sql_text
    ,v$sql_bind_capture.*
from v$sql_bind_capture
inner join v$sql on
    v$sql_bind_capture.hash_value = v$sql.hash_value
    and v$sql_bind_capture.child_address = v$sql.child_address
--Some unique string from your query
where lower(sql_text) like lower('%priceWorx.fdG2J(sysdate)%');

You probably would like to see the entire query, with all the bind variables replaced by their actual values. Unfortunately, there's no easy way to get exactly what you're looking for, because of the following issues.

V$SQL_BIND_CAPTURE doesn't store all of the bind variable information. The biggest limitation is that it only displays data "when the bind variable is used in the WHERE or HAVING clauses of the SQL statement."
Matching the bind variable names from the bind capture data to the query is incredibly difficult. It's easy to get it working 99% of the time, but that last 1% requires a SQL and PL/SQL parser, which is basically impossible.
SQL will age out of the pool. For example, if you gather stats on one of the relevant tables, it may invalidate all queries that use that table. You can't always trust V$SQL to have your query.

Which means you're probably stuck doing it the ugly way. You need to manually store the SQL and the bind variable data, similar to what user1138658 is doing.

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

You can do this with the dbms_output package. You can enable and disable the debug, and get the lines with get_line procedure.

I tested with execute immediate, inserting in a table and it works.

I recently answered another question with a example of using this.

edited May 23 '17 at 11:48

Community

1
1

answered Mar 07 '12 at 20:29

This question was edited after my answer. Get dbms_output in this case is not what the OP wants... – Mar 08 '12 at 18:55

score 0 · Answer 3 · edited Mar 08 '12 at 07:47

One possible solution of this is to create a table temp(id varchar2,data clob); in your schema and then put the insert statement wherever you want to find the parsed key

insert into temp values(seq.nextval,v_text);

For example

declare 
v_text varchar2(2000);
begin
v_text:='select * from emp'; -- your dynamic statement

insert into temp values(seq.nextval,v_text); --insert this script whenever you want to find the actual query 

OPEN C_CUR FOR v_text;

-----

end;

Now if you see the table temp, you'll get the data for that dynamic statement.

How to retrieve parsed dynamic pl Sql

3 Answers3