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?

score 17 · Answer 1 · edited Jan 27 '13 at 13:27

17

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

edited Jan 27 '13 at 13:27

Ben

51,770
36
127
149

answered Jan 27 '13 at 13:06

user2015502

197
1
2

1

@user2015502 have u tried this in a Hibernate formula? it responds always as table not found and I´ve got my query working in developer – eduyayo Sep 23 '15 at 20:14
Thanks for the query but it doesn't work for me. What I get is a BLOB with size 0. This method also need the inner select to filter: `single-row subquery returns more than one row`. – detoro84 May 23 '17 at 13:05
1

Only works for one column-select. Nice workaround, tho – Alfabravo Jan 19 '18 at 22:04

score 5 · Accepted Answer · answered Sep 02 '08 at 17:53

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.

score 2 · Answer 3 · answered Oct 06 '16 at 11:12

2

For query data, the solution of user2015502 is the smartest. If you want to insert or update LOB's AT the remote database (insert into xxx@yyy ...) you can easily use dynamic SQL for that. See my solution here:

answered Oct 06 '16 at 11:12

PT_STAR

505
1
4
13

score 1 · Answer 4 · answered Nov 19 '08 at 19:12

1

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

answered Nov 19 '08 at 19:12

Luis HGO

81
1
2

score 0 · Answer 5 · answered Sep 01 '08 at 16:35

0

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.

answered Sep 01 '08 at 16:35

hamishmcn

7,843
10
41
46

score 0 · Answer 6 · answered Sep 01 '08 at 16:39

0

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.

answered Sep 01 '08 at 16:39

morais

2,951
5
27
27

Best way to handle LOBs in Oracle distributed databases

6 Answers6

Linked