4

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.

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208

2 Answers2

1

Use Oracle function LENGTHB() to get this result. there is a way around convert CLOB to BLOB by using DBMS_LOB.CONVERTTOBLOB and use DBMS_LOB.GETLENGTH(). this is will return no of bytes.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Ajith Sasidharan
  • 1,155
  • 7
  • 7
1

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

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208