Steve Chambers Steve Chambers - 1 month ago 22
SQL Question

How to determine the length of a CLOB (in bytes) using the AL32UTF character set in Oracle?

I'm currently wondering if there is a way to find out the number of bytes used by a particular field value (which may or may not be longer than 4000 characters) in a SQL query.

dbms_lob.getLength() returns the number of characters not bytes and I can't just do a straight multiplication since there are a variable number of bytes per character in this character set. Briefly wondered about using dbms_lob.converttoblob() but this appears to need PL/SQL and I need to do this directly in a single query.

Now stumped! Any help much appeciated...

Answer

Update: As I haven't received a satisfactory answer yet I'm currently resorting to using dbms_lob.getlength() to get the number of characters and then multiplying by 2. This is based on a comment here about the AL32UTF8 character set:

https://forums.oracle.com/forums/thread.jspa?threadID=2133623

"Almost all characters require 2 bytes of storage with a handful of special characters requiring 4 bytes of storage."

Haven't verified how true this is but the person sounded like they knew what they were talking about so am currently using it as a "best guess".