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

sql oracle clob
rag picture rag · Nov 25, 2009 · Viewed 153.2k times · Source

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).

Answer

rag picture rag · Nov 25, 2009

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.

Related questions

How to query a CLOB column in Oracle

I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value. I tried using DBMS_LOB.substr(column) …

Read More
Search for a particular string in Oracle clob column

How to get a particular string from a clob column? I have data as below which is stored in clob column called product_details CALCULATION=[N]NEW.PRODUCT_NO=[T9856] OLD.PRODUCT_NO=[T9852].... -- with other text I would …

Read More
Extract data from XML Clob using SQL from Oracle Database

I want to extract the value of Decision using sql from table TRAPTABCLOB having column testclob with XML stored as clob. Sample XML as below. <?xml version="1.0" encoding="UTF-8"?> <DCResponse> <Status>Success</Status&…

Read More