Debug information for dynamic SQL string

[Benjamin L]

Hey Marco,

is it possible to show the content of large Text-Variables in Debugger? We neeed this to debug dynamic SQLs. If the variable is type of varchar2 it seems the value in "watches" switch to "(Long value)" after 1000 characters.
We tried to use clob, but the clob show's only about 120 characters.

A little example:
Using the variable lsSQL as varchar2, the SQL is shown until step 34, using clob only to step 4:

DECLARE
lsSQL CLOB; -- VARCHAR2(32767);
i INTEGER := 0;
BEGIN
lsSQL := 'SELECT SYSDATE FROM dual';
lsSQL := 'SELECT 0 from dual';
WHILE i < 100 LOOP
i := i + 1;
lsSQL := lsSQL || ' UNION ALL' || CHR(10) || 'SELECT ' || i || ' FROM dual';
END LOOP;
END;

Regards Benjamin

 

[Marco Kalter]

This is unfortunately an Oracle Debug API restriction. Values larger than 1000 bytes cannot be displayed.

 

[Benjamin L]

Hi Marco,

thanks for the answer. What abaout clobs? It shows less than 1000 characters, does it have the same restrictions?

regards Benjamin

 

[Marco Kalter]

Yes, the same restriction applies.