Skip to Main Content
  • Questions

  • Reading LOB column value from remote database

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, OmPrakash.

Asked: July 15, 2009 - 1:13 pm UTC

Last updated: July 16, 2009 - 2:26 pm UTC

Version: 10.2.0.3.0

Viewed 1000+ times

You Asked

Hi Tom,

Greetings!

Is there any way to extract LOB column information from remote database. For example, when we use the following statement, it returns an error

select CURRENT_PENDING_GROUPS from user2.table2@te01
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables.

Any help will be much appreciated.

Many thanks,
Om


and Tom said...

you would/could:

create global temporary table lob_cache ( x clob ) on commit delete/preserve rows;


then you would copy the lob locally:

insert into lob_cache select CURRENT_PENDING_GROUPS from user2.table2@te01 where ....;

and then you have them. The lob locator cannot be used over the dblink, so you bring the lob data to you.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

To get LOB Column value from remote database

Om, July 17, 2009 - 5:10 am UTC

Hi Tom,

Many thanks for your quick response.

This is very useful as it now gives me an idea to proceed with the application being developed to get required information from remote database.

Thanks again,
Om

Just an hint for a similar problem

Letizia, January 21, 2010 - 3:50 am UTC

I had a similar setting for a couple of years, loading LOB data from a remote DB into a global temporary table and then transferring to a local permanent table. It all worked fine until I decided that I needed to filter rows based on an INNER JOIN on a local table:

INSERT INTO TEMP_TABLE
(ID_COLUMN, DESCRIPTION_COLUMN, LOB_COLUMN)
(SELECT RT.ID_COLUMN, RT.DESCRIPTION_COLUMN, RT.LOB_COLUMN
FROM LOB_TABLE@REMOTE_DB RT
INNER JOIN LOCAL_TABLE LT ON LT.ID_COMMON = RT.ID_COMMON)

Oracle in this case cannot simply transfer lob data.

This was in the middle of several other changes, including changes to the GTT, and I took me a while to understand what was different. So I thought I'd share this and help someone else.

BTW: THANKS TOM! You always put me on the right track!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here