Search code examples
sqloracle-databaseclob

How to get size in bytes of a CLOB column in Oracle?

How do I get the size in bytes of a CLOB column in Oracle?

LENGTH() and DBMS_LOB.getLength() both return number of characters used in the CLOB but I need to know how many bytes are used (I'm dealing with multibyte charactersets).

Solution

  • After some thinking i came up with this solution:

     LENGTHB(TO_CHAR(SUBSTR(<CLOB-Column>,1,4000)))

    SUBSTR returns only the first 4000 characters (max string size)

    TO_CHAR converts from CLOB to VARCHAR2

    LENGTHB returns the length in Bytes used by the string.