Best way to handle LOBs in Oracle distributed databases

Question

If you create an Oracle dblink you cannot directly access LOB columns in the target tables.

For instance, you create a dblink with:

create database link TEST_LINK 
  connect to TARGETUSER IDENTIFIED BY password using 'DATABASESID';

After this you can do stuff like:

select column_a, column_b 
from data_user.sample_table@TEST_LINK

Except if the column is a LOB, then you get the error:

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

This is a documented restriction.

The same page suggests you fetch the values into a local table, but that is... kind of messy:

CREATE TABLE tmp_hello 
AS SELECT column_a 
from data_user.sample_table@TEST_LINK

Any other ideas?

Answer 1

The best solution by using a query as below, where column_b is a BLOB:

SELECT (select column_b from sample_table@TEST_LINK) AS column_b FROM DUAL