Toad World® Forums

Improve handling of CLOB/VARCHAR2/BLOB/XMLTYPE values and expressions


I’ve got a few ERs for CLOB/VARCHAR2/BLOB/XMLTYPE:

  1. OUT argument values (guess it’s the same for IN argument values): The value is prefixed with 'First 4000 chars: ', which is not the case with VARCHAR2. These two should be similar so I suggest that VARCHAR2 is changed to the same behavior as it’s important to show that the value is truncated. In fact the text is wrong as it’s only the first 3982 characters (the text with the prefix is 4000 characters long).

  2. Test type ‘IS NULL’: If the value is NOT NULL, the result is ‘Value IS NOT NULL’. For VARCHAR2 it’s 'Value IS NOT NULL: ’ + the first 3981 characters of the string. This should be the same for CLOB as the value is missing entirely.

  3. Save the full value of XMLTYPE/VARCHAR2/CLOB/BLOB expressions in QU_RESULT_FULL_VALUE. Currently, if an outcome is based on an expression only the truncated version of the value is found in QU_RESULT.DESCRIPTION and no row is created in QU_RESULT_FULL_VALUE for expressions. Doing so will greatly improve debugging of unexpected results with expressions. For XMLTYPE, GETCLOBVAL (potentially GETCLOBVAL(0, 2) for Oracle 11.1 or newer) should be called in order to get the CLOB representation. For BLOBs, it’s the hexadecimal representation that is saved.

Thanks in advance.