How do I debug Oracle dynamic sql in sqlplus?

Question

I have a PL/SQL statement that uses EXECUTE IMMEDIATE to execute a query. However, I'm having difficulty figuring out how to even get the text of the query that's being executed. I can't use dbms_output as the query is greater than 255 characters. Is there any way to make sqlplus echo the string that's passed in to EXECUTE IMMEDIATE?

Answer 1

What version of Oracle are you using? 255 is the default line length for DBMS_OUTPUT.PUT_LINE(). Before 10g we could display 1048 characters in a single call. Since then it is 32K.

Alternatively you should consider using an IDE which supports DBMS_DEBUG. For instance, SQL Developer does so, and it is free from Oracle. Find out more.

Answer 2

You can try to attach a profiler to the database (honestly I have only done that for SqlServer) and run the procedure since the profiler will show any query made to the DB you will be able to pick it up there and do the necessary debugging. Hope it helps..

Answer 3

How to print large strings N characters at a time. Modify to suit your needs.

FOR i IN 0..10 LOOP
  dbms_output.put_line(substr(my_very_long_string,i*100+1,100));
END LOOP;

Answer 4

You could insert the string into a logging/temporary table and examine it.