LarsBlog

I've found it a bit of a pain today to find a (relatively) simple way to work with XML in a BLOB/CLOB field in an Oracle 11g database via Oracle SQL Developer, so I want to note it for future reference.

To UPDATE a BLOB/CLOB with a string, the string must be cast to RAW.  Declaring this as a longliteral and using it as a parameter in a procedure gets around a 2000 character limit with casting:

DECLARE
    LONGLITERAL RAW(32767) := UTL_RAW.CAST_TO_RAW('<?xml version="1.0" encoding="UTF-8"?> 
<someXml> ... (Can be greater than 2000 characters) ... </someXml>');
BEGIN
EXECUTE IMMEDIATE
    'UPDATE tablename SET xml_field = :1 WHERE id = 20' USING LONGLITERAL;
END;

To SELECT from a BLOB/CLOB and have the output displayed as text, you have to cast the BLOB/CLOB field to VARCHAR2.  Unfortunately casting can only be performed on strings up to 2000 characters, so the value must be truncated for this method to work:

SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(xml_field,2000,1))
FROM tablename
WHERE id = 20

If the above doesn't cut it for you, there are less simple methods for the above that involve looping and concatenating the field together in 2000 character blocks to work around the size limit.