Best way to handle LOBs in Oracle dblink'ed tables

Question

Hi,

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 documented here:

http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96591/adl04mng.htm#98328

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 ideas? Thanks

Answer 1

Do you have a specific scenario in mind? For example, if the LOB holds files, and you are on a company intranet, perhaps you can write a stored procedure to extract the files to a known directory on the network and access them from there.

Answer 2

In this specific case can the only way the two systems can communicate is using the dblink.

Also, the table solution is not that terrible, it's just messy to have to "cache" the data on my side of the dblink.

Answer 3

Yeah, it is messy, I can't think of a way to avoid it though.
You could hide some of the messiness from the client by putting the temporary table creation in a stored procedure (and using "execute immediate" to create they table)
One thing you will need to watch out for is left over temporary tables (should something fail half way through a session, before you have had time to clean it up) - you could schedule an oracle job to periodically run and remove any left over tables.

Answer 4

You could use materalized views to handle all the "cache" management. It´s not perfect but works in most cases :)