Selecting and updating text in an Oracle BLOB or CLOB

Filed under: Oracle — lars @ 03:05:31 am

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:

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

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:

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.


  • Demetrio Dias
    Thanks man! Works fine for me!

    Comment by Demetrio Dias [Visitor] — 01/28/17 @ 00:03

Leave a comment

Allowed XHTML tags: <p, ul, ol, li, dl, dt, dd, address, blockquote, ins, del, span, bdo, br, em, strong, dfn, code, samp, kdb, var, cite, abbr, acronym, q, sub, sup, tt, i, b, big, small>

(Line breaks become <br />)
(Set cookies for name, email & url)

powered by  b2evolution