How to Return CLOB Size in Bytes like LENGTHB Function of CHAR/VARCHAR2 (Doc ID 790886.1)

Goal

The dbms_lob.getlength function returns the number of CHARACTERS not (as often assumed) the nr of BYTES of the CLOB.
This is the same behavior as LENGTH (column) who also returns the amount of characters.

In single byte NLS_CHARACTERSET databases (WE8ISO8859P1, WE8MSWIN1252 etc) it happens that every character is one byte, so there the nr of characters happens to be the same as the nr of bytes .
This is however not true in multibyte databases like AL32UTF8 See <Note 788156.1> AL32UTF8 / UTF8 (Unicode) Database Character Set Implications, section "B.1) Storage."

Seen the dbms_lob.getlength function returns the number of characters, not the number of bytes of the CLOB then,  when using a multibyte characterset like AL32UTF8, if the data only contains US7ASCII (for ex 'abc') then the character length of the data ('abc') is the same as the byte length (just like with a single byte NLS_CHARACTERSET because for US7ASCII character 1 character is one byte in UTF8/AL32UTF8), but if the data contain non-US7ASCII characters ('abcą' for example) the length in characters (as reported by dbms_lob.getlength) is NOT the same as the byte length of the string because non-US7ASCII characters are 2 or more bytes in an mulitbyte characterset (ą is 2 bytes for example).

To know wherther your database is a single or multibyte please do as follows:

What is needed is a function to get clob length in bytes if a database characterset is multibyte such as AL32UTF8.
An enhancement request via <Bug 7156454> has been filed to get this functionality and is under consideration by development.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.