Is 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 an Oracle 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.
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".