Friday, December 23, 2011

Dbms_xmlgen Convert Error

When running through a large amount of data stored in clobs, I was doing
    DBMS_XMLGEN.CONVERT(DBMS_LOB.SUBSTR(lobcolumn,4000,1))
to first truncate my clob and make it into a varchar, then convert xml/html entities to regular characters.  This was throwing
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
when I got to a value which was actually more than 4000 characters long.  I didn't realize that xmlgen.convert can work on lobs though, so just reversing the order of operations resolved the error:
    DBMS_LOB.SUBSTR(DBMS_XMLGEN.CONVERT(lobcolumn),4000,1)
and it's good to go.

No comments: