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:
technology.amis.nl/2012/07/02/select-a-blob..
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;