# 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;