rshprakash

 Profile Answers by rshprakash 

• Aug 9th, 2009

 

dbms_output is the best option. rather we go for line by line debugging using any IDE tool like plsql developer or toad

chill_kamal

 Profile Answers by chill_kamal 

• Aug 31st, 2009

 

Better insert the dynamic SQL statement into a log table. To see the Exact query or if you have Quest Software (TOAD) use the SQL monitor or session browser to see what query executed.

chinnakathir_be

 Profile Answers by chinnakathir_be 

• Dec 27th, 2009

 

Log the dynamicaly builded script in the log table and manually execute for testing.

abey238

 Profile Answers by abey238 

• Jan 19th, 2010

 

Take the appropriate trace (database trace or the application trace) for the process and convert the raw trace to tkprof. Use the tkprof to analyze the dynamic query.

sanjeevlabh

 Profile Answers by sanjeevlabh Questions by sanjeevlabh

• Jan 22nd, 2010

 

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. 

Rabindra_Kumar

 Profile Answers by Rabindra_Kumar 

• Mar 20th, 2010

 

As per my experience the best way to debug the dynamic sql is to insert the records of all the stages of that programme in a temporary table. After running the programme you can get the stages from the temporay table and you can debug the programme. you can see that which block has returned what. A correct output has been given or not.

shivaranjani.p

 Profile Answers by shivaranjani.p 

• Oct 6th, 2010

 

Hi,

You need to store each and every line of the dynamic sql in a file using UTL_FILE Package before you could execute it, so that you can refer the same file after executing the code, this here serves you as a log file for debugging.

For Ex:

UTL_FILE.PUT_LINE(v_filehandle,p_string_in);

where p_string_in is dynamic SQL for each line.

Suppose if my dynamic SQL consists of following 3 lines.

------

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS''

END;

------

then you will call UTL_FILE.PUT_LINE function 3 times

as follows:

UTL_FILE.PUT_LINE(v_filehandle, 'BEGIN');

UTL_FILE.PUT_LINE(v_filehandle, 'EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS''');

UTL_FILE.PUT_LINE(v_filehandle, 'END');

kumardba

 Profile Answers by kumardba 

• Oct 7th, 2010

 

You can try to debug Dynamic SQL using SQL Advisor Tool or TKPROF tool