DBMS_LOB For DBAs – Conquer Your Fear Of LOBS

Are LOBS your favorite data type?

Not for me. And I strongly believe not for you either.

Why is that? First of all, we don’t work with LOBS on a daily basis, and when we do, it gives us more headaches than fulfillment.
There is a list of CAN’Ts and DON’Ts with LOBS, which we don’t remember, and if I am right, you might not even know what exactly a LOB is, and how/what is stored in it.

Because of this, we feel fearful of even coming closer or touching LOBS.

That is why today, I’ll give you a very brief (easy to remember) overview of LOBS, and some really useful tips on how to select and compare LOBS, ’cause really that is what we get asked for many times.

1.What Are LOBS?
2.How Can I See The Value Of A LOB Column?
3.How Can I Compare Two LOB Values?
4.How Can I Compare Two Tables With LOBS?

1.What Are LOBS?

LOBS stand for Large Objects, and are a set of datatypes (BLOB, CLOB, NCLOB) designed to hold big amounts of data.
Usually semi-structured (XML document) or unstructured (an image) data is stored in a LOB datatype.
The data can be stored inside the database, or in OS files that are accessed from the database.

When semi-structured data is stored in the database, it is not broken down into smaller logical units. With this type
of data, usually CLOB or NCLOB datatype is used. Did you know, C stands for Character?

When unstructured data is stored in the database, this also is not broken down into smaller units. An image consists of streams of 1s and 0s, to switch the pixels on/off, and that is how the image is stored. With this data type, usually a BLOB or BFILE is used. Did you know, B stands for Binary?

2.How Can I see the value of a LOB column?

When querying a table with a LOB datatype, you can actually see the value stored in the databases. This doesn’t mean, it is readable output. If data is binary, then it is binary, that is what you will see.

To see the content of a LOB column, BLOB or CLOB you can use a couple of methods:

select lob_column from table;
select dbms_lob.substr(lob_column,4000) from table;

3.How Can I Compare Two LOB Values?

This is one of the most popular questions I get asked in regards to LOBS. If I want to compare two LOBS, how can I do it?

Well, let’s think for a second.

With a regular data type, you could just perform a MINUS operation on the 2 tables for the 2 columns.

This is a big NO-NO for LOBS! You’ll get instantly an error:

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected – got BLOB

When comparing LOBS, you really need to know if the data is the same or not in the two columns.

Here to help is Oracle’s dbms_lob.compare package.

When using this package, if the return code is 0 the 2 lobs you are comparing are identical, and if te return code is different from 0 values are different.

In order to get a confirmation on a comparison, all you need to do is run this select statement:

select distinct case dbms_lob.compare(a.map, b.map) 
       when 0 then 'LOB Columns Are Identical.'
       else 'LOB Columns Are Not Identical.' end  as Comparison_Output
from maps1 a, maps2 b  
where a.mapid=b.mapid;     

COMPARISON_OUTPUT
------------------------------
LOB Columns Are Identical.

4.How Can I Compare Two Tables With LOBS?

Let’s stretch our mind a bit further, how can we apply the above in order to compare 2 tables, and return the rows that have different LOB values?

All we need to do, is join the 2 tables on the primary key, and list the primary key columns in the SELECT clause, for the rows that are different.

select a.mapid 
from maps1 a, maps2 b
where a.mapid=b.mapid
and dbms_lob.compare(a.map, b.map) <> 0;

Now you can set aside your fear of the unknown, and replace it with knowledge of LOBS!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana