LOBs Over a Database Link

There are lots of pluses and minuses to db links, but they are certainly easy and used in the right context they work very well. I admit that I sometimes use them when there is a better technical solution--just because it is so easy and the better solution may not be worth the time.

The case of LOBs over db links can be tricky. You can't select a lob over a db link in SQL or PL/SQL:

select my_blob
from my_table@mylink;

results in

ORA-22992: cannot use LOB locators selected from remote tables

There are several techniques that work. You CAN do

insert into my_local_table (the_blob)
select my_blob
from my_table@mylink;

There is another interesting technique here:

https://technology.amis.nl/2012/07/02/select-a-blob-across-a-database-link-without-getting-ora-22992/

We recently had a requirement to just show the first few hundred characters of a lob over a db link. It was a complicated query and the developer wrote something like this:

select local.c1, local.c2, remote.c3
, dbms_lob.substr(remote.my_blob, 200, 1) blob200
from local_table local
inner join remote_table@mylink remote on remote.c2 = local.c2;

This worked fine in the development and test environment. In production it gave the ORA-22992. It depended on how the optimizer chose to run the query. If the dbms_lob.substr ran on the remote database it was fine, but if it had to pull the blob to the local db it was a problem.

We solved it by forcing the the dbms_lob.substr to run on the remote node:

select local.c1, local.c2, remote.c3
, dbms_lob@mylink.substr(remote.my_blob, 200, 1) blob200
from local_table local
inner join remote_table@mylink remote on remote.c2 = local.c2;