Dbms_lob.getlength() vs. length() to find blob size in oracle

sql

blob

oracle

database

byAnton Shumikhin·Jan 14, 2025

Go for DBMS_LOB.GETLENGTH when looking for BLOB size in bytes. It's reliable and unaffected by character set nuances:

-- When life gives you BLOBs, count them in bytes!
SELECT DBMS_LOB.GETLENGTH(blob_column) FROM your_table;

So remember - for BLOBs, use DBMS_LOB.GETLENGTH. LENGTH is just fine for CLOB.

Why dbms_lob.getlength() for BLOBs?

DBMS_LOB.GETLENGTH is your tool of choice for deep-diving into BLOB dimensions. No character set clownery can fool it, it gives you the byte-for-byte measurement you need, as BLOBs are binary objects.

Beware of length() for BLOBs!

Using LENGTH() on a BLOB is a little bit like measuring your height with a bucket - it'll give you a ballpark figure, but the devil's in the detail. Dual-byte characters are a stumbling block for LENGTH(), since it counts characters, not bytes.

Multi-byte characters? No problem

When wrestling with multi-byte characters, DBMS_LOB.GETLENGTH won't let you down. Use LENGTH() for CLOB, where characters are more meaningful than bytes.

Practical example - image sizing

Your ".bmp" image files sheltering in your BLOBs can't hide from DBMS_LOB.GETLENGTH, it gives you the accurate byte size. This can be a game-changer for image retrieval, optimizing storage or network transmissions.

Pitfalls and how to avoid them

Don't be fooled by length()

Trying to bend LENGTH() to your will when sizing your BLOBs can lead to performance nightmares or data truncation horrors. Length isn't everything!

Multibyte mischief

Multi-byte character sets can affect your BLOB sizes, and this is often overlooked. A 'AL32UTF8' character set, with multi-byte characters, adds a twist - DBMS_LOB.GETLENGTH is the byte-accurate measure you need.

Useful guidance

Know your character set

Are you dealing with 'WE8ISO8859P1' or 'AL32UTF8'? Whatever your characters are up to, ensure your byte count is on the money - go with DBMS_LOB.GETLENGTH.