Folder Name

other Auditor
4

I have an addendum on this.

I just went through the process of adding this field to a mart for reporting and found that BO suffixes a NULL to every string record in the CLOB.

This is not a problem when you are pulling just the one field, but if you say try to concatenate the document_name on the end of the folder_name you’re not going to see the document name.

The data WILL be in there, but the embedded NULL fools your display routines so that the display of the string ends when it encounters the embedded NULL.

We didn’t care about the long XML fields and filtered by detail_type_id, so the arbitrary chop at 256 bytes was OK for us. YMMV.

SELECT
CAST( DBMS_LOB.SUBSTR( a.detail_text, LEAST( DBMS_LOB.GETLENGTH( a.detail_text ) - 1, 256 ), 1 ) AS VARCHAR( 256 ) ) AS detail_text
FROM   php_boe_audit.audit_detail a

This is a fragment of a larger SQL. The rest of the SQL is completely sane.

Note also that you cannot SELECT a CLOB across a DBLINK, so the conversion to a rational data type has to happen (in a view) on the server that hosts the CLOB data.

(This is all ‘Oracle speak’)

kbrazell :us: (BOB member since 2003-08-19)