Oralce Dynamic SQL Debugging |Oracle PL/SQL Interview Questions for TCS,Wipro,Infosys,IBM,and Accenture

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');