Home > problem-cause-solution > ORA-22992 How to deal with LOB locators in remote tables

ORA-22992 How to deal with LOB locators in remote tables


Problem:

“ORA-22992: cannot use LOB locators selected from remote tables” error message
while selecting from table over database link having blob data type column.

Cause:

LOB columns referenced over dblink together with other columns.

From Oracle documentation:

“In statements structured like the preceding examples, only standalone LOB columns are allowed in the select list”.

Solution:

In order to select into or CTAS from remote table containing LOB field (CLOB in my case), you must insert without selecting LOB column and then making an update only selecting LOB column.

update myschema.mytable t1
set lob_column=(select lob_column from myschema.mytable@REMOTE_LIVE t2
where t1.id=t2.id);

It is also strange that; below statement is not working:

select lob_column from  myschema.mytable@REMOTE_LIVE

Thanks to

user628981

http://forums.oracle.com/forums/thread.jspa?messageID=1252302


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: