Oracle data types BLOB, CLOB and NCLOB

LOB locator

A LOB locator is a (opaque?) data structure that allows to access the data of a LOB.

In a database table, the LOB locator is stored with the table rows but the actual LOB data might be stored in a separate storage (usually in a separate tablespace).

An empty LOB locator is returned by empty_blob() and empty_clob() or dbms_lob.createTemporary.

LOB locators can be manipulated with the dbms_lob package. In fact, all procedures and functions of this package must be called with a lob locator.

Three types of LOBs

There are three types of LOBs:

Persistent LOBs	The data of persistent LOBs is stored with tables and are subject to transactions.
Temporary LOBs	They reside in either a process's PGA or temporary tablespace, depending on their size.
BFILEs	The data is stored in a file system.

LOBs returned from SQL or PL/SQL built-in functions are temporary LOBs.

v$temporary_lobs shows a session's number of temporary LOBs.

Internal and external LOBs

Because persistent and temporary LOBs are stored in the database, they're referred to as internal lobs.

BFILEs, on the other hand, are not stored in a database and are therefore referred to as external LOBs.

Available data types for internal LOBs are CLOB, NCLOB and BLOB.

LOB Index

Oracle creates an index for each LOB column. The name of these indexes start with SYS_IL and end with $$.

create table tq84_xyz (
   id  integer primary key,
   txt clob
);

select
   index_name
from
   user_lobs
where
   table_name  = 'TQ84_XYZ' and
   column_name = 'TXT';
--
-- SYS_IL0000107991C00002$$

The index is registered in the data dictionary:

select * from user_indexes where index_name = 'SYS_IL0000107991C00002$$';

But is has no ordinary columns, the following statement returns nothing:

select * from user_ind_columns where index_name = 'SYS_IL0000107991C00002$$';

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

When concatenating strings (varchar2) and the resulting length becomes larger than 64 KB, PL/SQL throws an ORA-06502: PL/SQL: numeric or value error: character string buffer too small error message.

This error can be prevented when (at least) the first element in such a concatenation is a clob. In the following example, this is achieved using the to_clob(…) function:

create table tq84_clob (
   txt clob
);

declare
   c clob;
   vc_32K_minus_1 varchar2(32767) := rpad('*', 32767, '*');
begin

   c := vc_32K_minus_1 || vc_32K_minus_1;

   begin
      c := vc_32K_minus_1 || vc_32K_minus_1 || '**';
   exception when others then
   --
   -- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
   --
      dbms_output.put_line(sqlerrm);
   end;

   c := to_clob(vc_32K_minus_1) || vc_32K_minus_1 || vc_32K_minus_1 || vc_32K_minus_1 || vc_32K_minus_1 || vc_32K_minus_1;

   insert into tq84_clob values(c);

end;
/

select dbms_lob.getLength(txt) len_actual, 6*32767 len_expected from tq84_clob;

drop table tq84_clob;

Github repository oracle-patterns, path: /SQL/datatypes/lob/ORA-06502.sql

Group by LOB columns not permitted

Oracle does not allow to group by LOB-columns:

create table tq84_grpby_clob (
  id number,
  txt clob
);

insert into tq84_grpby_clob values (1, 'foo');

The following select statement causes the error ORA-00932: inconsistent datatypes: expected - got CLOB:

select
   max(id),
   txt
from
   tq84_grpby_clob
group by
   txt;

JDBC

With JDBC 4.0, LOBs should be manipulated with the following interfaces and implementations: (which provide random access to the data)

Interface	Implementation
`java.sql.Blob`	`oracle.sql.Blob`
`java.sql.Clob`	`oracle.sql.clob`
`java.sql.NClob`	`oracle.sql.NClob` (In `ojdbc6.jar`)

Insert and select BLOC/CLOB examples

SQL*Loader example: load binary data (blob)

A .NET example that demonstrates the Microsoft/dot-net class Oracle.DataAccess.Types.OracleBlob and a PowerShell example that demonstrates writing a Oracle.DataAccess.Types.OracleClob to a file.

This Java/JDBC example inserts the content of (text-)files into CLOBs.

This SQLcl example puts an image (png) into a BLOB and then gets it again and stores it locally.

Query to determine LOB segment sizes

select
   tab.owner,
   tab.table_name,
   col.column_name,
   col.data_type,
   to_char(los.bytes / 1024/1024, '999,999,990') lob_seg_mb,
   to_char(ixs.bytes / 1024/1024, '999,999,990') ind_seg_mb,
   col.avg_col_len,
   to_char(tab.num_rows          ,'999,999,990') num_rows,
   col.last_analyzed                             col_last_analyzed,
   col.last_analyzed                             col_last_analyzed,
   col.sample_size,
   lob.segment_name   lob_seg_nam,
   lob.index_name     lob_ind_nam
from
   dba_tables           tab                                                     left join
   dba_tab_columns      col on tab.table_name            = col.table_name   and
                               tab.owner                 = col.owner            left join
   dba_lobs             lob on col.table_name            = lob.table_name   and
                               col.column_name           = lob.column_name  and
                               col.owner                 = lob.owner            left join
   dba_segments         los on lob.segment_name          = los.segment_name and
                               lob.owner                 = los.owner            left join
   dba_segments         ixs on lob.index_name            = ixs.segment_name and
                               lob.owner                 = ixs.owner
where
-- tab.owner      = user                                   and
   col.data_type like '%LOB%'
order by
   los.bytes desc  nulls last

TODO

BFILENAME

Use the shell to create a file on the server's filesystem:

$ mkdir /home/oracle/bfiles
$ echo 'Hello world' > /home/oracle/bfiles/test.txt

In the database:

create directory tq84_bfile_dir as '/home/oracle/bfiles';

create table tq84_bfiles (
   filename varchar2(255) primary key,
   data     blob
)
lob (data) store as securefile;

insert into tq84_bfiles values ('test.txt', bfilename('TQ84_BFILE_DIR', 'test.txt'));

select
   to_clob(utl_raw.cast_to_varchar2(data)) data
from
   tq84_bfiles
where
   filename = 'test.txt';
--
-- select
--    to_clob(utl_raw.cast_to_varchar2(data)) data
-- from
--    tq84_bfiles
-- where
--    filename = 'test.txt';

Then, on the shell:

$ echo 'good by' > /home/oracle/bfiles/test.txt

This has no effect to the table, the BLOB is stored and is not modified by the modifcation in the filesystem.