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...
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:
"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".