What is the best technique of debugging large and fragmented dynamic SQL?

The biggest challenge in debugging dynamic sql is to re-construct the actual query that had been sent to the sql parser at run time. The complexity increase as the size and number of fragmentation of the query increases.

As per my experience to debug dynamic sql the best method is to store the sql string in a table before execution. If the dynamic sql size is small then a simple varchar2 data type of the column storing the sql string is fine. But if the query size is quite big i.e. number of characters exceeding 2000 then the best method is by storing it in a column of debug table having CLOB data type of the field storing the sql string.

Personally I feel the CLOB method is a better choice since it would work under all circumstances.

No comments:

Post a Comment

Please Provide your feedback here