Dbms_Lob.Getlength() VS. Length() to Find Blob Size in Oracle

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

length and dbms_lob.getlength return the number of characters when applied to a CLOB (Character LOB). When applied to a BLOB (Binary LOB), dbms_lob.getlength will return the number of bytes, which may differ from the number of characters in a multi-byte character set.

As the documentation doesn't specify what happens when you apply length on a BLOB, I would advise against using it in that case. If you want the number of bytes in a BLOB, use dbms_lob.getlength.

Extract BLOB with length greater than 4000 in Oracle SQL

I found a simple way to get the output by using to_clob function

select to_clob(BYTES_) from ACT

Oracle Blobs - store size or calculate?

I did a quick check selecting a BLOB from a table and then a LENGTH(BLOB) and DBMS_LOB.GETLENGTH(BLOB).
When selecting the BLOB itself, I got 44 consistent gets. When I selected the length (by either method) I got 7 consistent gets.

Based on that, when I get the length, it does not retrieve the entire blob and calculate the length. It is sensible to assume that the length it stored at the start of the BLOB (like they length of a VARCHAR2 value is stored) and this is used directly.

As such, there shouldn't be a great overhead in deriving the length rather than storing it. It also reduces the chance of an inconsistency.

How to get size in bytes of a CLOB column in Oracle?

After some thinking i came up with this solution:

 LENGTHB(TO_CHAR(SUBSTR(<CLOB-Column>,1,4000)))

SUBSTR returns only the first 4000 characters (max string size)

TO_CHAR converts from CLOB to VARCHAR2

LENGTHB returns the length in Bytes used by the string.

What is an elegant way to return a readable 'file size' of a file stored in an oracle blob column using SQL?

The 'readable' part is what I should have emphasized more. Here is what I put together for now.

WITH file_sizes AS
     (SELECT 1048576 MEGABYTE, 1024 KILOBYTE,
             DBMS_LOB.GETLENGTH (BLOB_COLUMN) byte_size
        FROM BLOB_COLUMN)
SELECT (CASE TRUNC (byte_size / MEGABYTE)
           WHEN 0
              THEN TO_CHAR ((byte_size / KILOBYTE), '999,999') || ' KB'
           ELSE TO_CHAR ((byte_size / MEGABYTE), '999,999.00') || ' MB'
        END
       ) display_size
  FROM file_sizes

Output:

DISPLAY_SIZE  
--------------
       1.88 MB
     433 KB   
     540 KB   
     333 KB   
       1.57 MB
       1.17 MB

Explain why we need to add 1 to nvl(dbms_lob.getlength(column_name_blob),0) OR nvl(vsize(column_name),0)

A row is stored (more or less) as

 [row header]
 [length of column1] [column1 data]
 [length of column2] [column2 data]
 [length of column3] [column3 data]
 ...

Where [row header] consists of [flag byte][lock byte][columns count], ie 3 bytes.

Let's look at simple example - this is a dump info of one row from data block dump:

tl: 44 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [22]
 4d 61 6e 20 57 68 6f 20 53 6f 6c 64 20 54 68 65 20 57 6f 72 6c 64
col 1: [ 3] c2 14 47
col 2: [13] 42 6c 6f 6f 64 79 20 47 6f 6f 64 21 21

Here

• tl - total length of the row - 44 bytes - oracle doesn't store it, oracle just calculated it for us
• fb - flag byte
• lb - lock byte
• cc - column length
• [N] - length of the column data
• and column data after that

Let's calculate the length of this row manually:

• Row header: flag byte + lock byte + columns' count = 3 bytes
• Column 0: column length(1 byte) + 22 b = 23 bytes
• Column 1: column length(1 byte) + 3 b = 4 bytes
• Column 2: column length(1 byte) + 13 b = 14 bytes

So total = 3 + 23 + 4 + 14 = 44 bytes and we can see it's equal to tl, so we calculated it correctly.

Of course, in case of migrated or chained rows you will have more row headers, so you will need to add their lengths too.

In short: you can use this simplified formula: 3 (row header) + columns count + vsize() of all columns values, for example, for a table with 3 columns (without LOBs) considering nulls:

select
   3 -- row header
  +nvl(vsize(col0),1) -- even if other columns are nulls too, null will be stored as 1 byte (0xFF)
  +nvl(vsize(col1), case when col2 is null then 0 else 1 end) -- 0 if there is no data after this column
  +nvl(vsize(col1),0) -- oracle doesn't store trailing NULL-valued columns in row
from t

But! it's not about LOBs:
First of all it depends on ENABLE or DISABLE STORAGE IN ROW option.

LOB columns store locators that reference the location of the actual LOB value. This section describes how to enable or disable storage in a table row.

Actual LOB values are stored either in the table row (inline) or outside of the table row (out-of-line), depending on the column properties you specify when you create the table, and depending the size of the LOB. The ENABLE | DISABLE STORAGE IN ROW clause is used to indicate whether the LOB should be stored inline (in the row) or out-of-line.

If ENABLE STORAGE IN ROW is set, the maximum amount of LOB data stored in the row is 4000 bytes. This includes the control information and the LOB value.

So in case of Inline and short lobs (<3960bytes) you can count their length, but not for long lobs: first of all you need to count a length of lob locator + lob index + count a number of required chunks to store data and multiply it on chunk size. And do not forget about LOB retention (or pctversion): You may will need to count also old versions of your LOBs

Oracle - measure time of reading blob files using sql

Not quite sure what you're trying to achieve, but you can get some timings in a PL/SQL block using dbms_utility.get_time as LalitKumarB suggested. The initial select is (almost) instant though, it's reading through or processing the data that's really measurable. This is reading a blob with three different 'chunk' sizes to show the difference it makes:

set serveroutput on
declare
  l_start number;
  l_blob blob;
  l_byte raw(1);
  l_16byte raw(16);
  l_kbyte raw(1024);
begin
  l_start := dbms_utility.get_time;
  select b into l_blob from t42 where rownum = 1; -- your own query here obviously
  dbms_output.put_line('select: '
    || (dbms_utility.get_time - l_start) || ' hsecs');

  l_start := dbms_utility.get_time;
  for i in 1..dbms_lob.getlength(l_blob) loop
    l_byte := dbms_lob.substr(l_blob, 1, i);
  end loop;
  dbms_output.put_line('single byte: '
    || (dbms_utility.get_time - l_start) || ' hsecs');

  l_start := dbms_utility.get_time;
  for i in 1..(dbms_lob.getlength(l_blob)/16) loop
    l_16byte := dbms_lob.substr(l_blob, 16, i);
  end loop;
  dbms_output.put_line('16 bytes: '
    || (dbms_utility.get_time - l_start) || ' hsecs');

  l_start := dbms_utility.get_time;
  for i in 1..(dbms_lob.getlength(l_blob)/1024) loop
    l_kbyte := dbms_lob.substr(l_blob, 1024, i);
  end loop;
  dbms_output.put_line('1024 bytes: '
    || (dbms_utility.get_time - l_start) || ' hsecs');
end;
/

For a sample blob that gives something like:

anonymous block completed
select: 0 hsecs
single byte: 950 hsecs
16 bytes: 61 hsecs
1024 bytes: 1 hsecs

So clearly reading the blob in larger chunks is more efficient. So your "measure time of reading it" is a bit flexible...

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

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

How do I get textual contents from BLOB in Oracle SQL

First of all, you may want to store text in CLOB/NCLOB columns instead of BLOB, which is designed for binary data (your query would work with a CLOB, by the way).

The following query will let you see the first 32767 characters (at most) of the text inside the blob, provided all the character sets are compatible (original CS of the text stored in the BLOB, CS of the database used for VARCHAR2) :

select utl_raw.cast_to_varchar2(dbms_lob.substr(BLOB_FIELD)) from TABLE_WITH_BLOB where ID = '<row id>';

---

Related Topics