Skip to Main Content
  • Questions
  • How to select table from remote database having clob field

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, vrishali.

Asked: October 24, 2000 - 9:25 am UTC

Last updated: October 06, 2016 - 12:51 am UTC

Version: 8.1.6

Viewed 100K+ times! This question is

You Asked

Hi Tom,

I have a table on remote database having clob datatype of one of its field. I want to select clob field from that table using database link.

Select dbms_lob.substr(field_name,4000,1) from schema_name.table_name@db_link;

Which is not returning its result but giving error-

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

Could you please suggest me any solution to see the data of remote table having clob from local database?
--------------------------------------------------------------
Thanks
Vrishali

and Tom said...

2 methods:

1) create a view on the remote site that selects the dbms_lob.substr. You can then select from that view over the dblink.

2) using a temporary table, copy the lob over to work with it. For example:

scott@ORA8I.WORLD> create global temporary table foo ( x clob );
Table created.

scott@ORA8I.WORLD> insert into foo select x from t@ora816dev.us.oracle.com;

1 row created.

scott@ORA8I.WORLD> select * from foo;

X
--------------------------------------------------------------------------------
Hello World

Rating

  (21 ratings)

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

Comments

Thanks a lot, very useful

Chris, September 12, 2003 - 8:56 am UTC

On metalink, every one said there's no solution ....
Oracle can't do that ...
but you Tom, have 2 solutions and it works very well !

Thanks

Inconstancies in operation

LC, April 29, 2004 - 6:19 pm UTC

Hey Tom... love the site. Thanks for all your help.

So I'm a bit confused on why one works and not the other.  Any insite would be appreciated. I'm 8.1.7.4 on windows.

Thanks.

SQL> desc doc_template
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_DOC                                    NOT NULL NUMBER
 BL_TEMPLATE                                        BLOB
 ID_CR                                     NOT NULL NUMBER
 TS_CR                                     NOT NULL DATE
 ID_UP                                     NOT NULL NUMBER
 TS_UP                                     NOT NULL DATE
 CD_CNTY                                   NOT NULL NUMBER
 TX_IMAGE_NM                                        VARCHAR2(25)
 TX_PAGE                                            VARCHAR2(100)
 TX_FILE_NAME                                       VARCHAR2(60)
 TX_FORM_NAME                                       VARCHAR2(20)
 TX_TEMPLATE_NAME                                   VARCHAR2(100)

SQL> insert /*+append */ into doc_template 
  2  select *   from ( select a.*, rownum rnum from (  
  3  select * from doc_template@gray order by rowid ) a  where rownum <= 15 ) 
  4  where rnum >= 5;
select * from doc_template@gray order by rowid ) a  where rownum <= 15 )
       *
ERROR at line 3:
ORA-22992: cannot use LOB locators selected from remote tables


SQL> 
SQL> 
SQL> insert /*+append */ into doc_template 
  2  select *  from  doc_template@gray  where rownum <= 15 ;

15 rows created.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL>  

Tom Kyte
April 29, 2004 - 6:27 pm UTC

explain plan them and show us the plans.

tkprof from example

LC, May 03, 2004 - 11:34 am UTC

The following statement encountered a error during parse:

insert /*+append */ into doc_template
select * from ( select a.*, rownum rnum from (
select * from doc_template@gray order by rowid ) a where rownum <= 15 )
where rnum >= 5
==============
Error encountered: ORA-22992
********************************************************************************

insert /*+append */ into doc_template
select * from doc_template@gray where rownum <= 15

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.71 1.39 0 11 1019 15
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.71 1.39 0 11 1019 15

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5 (SYSTEM)

Rows Row Source Operation
------- ---------------------------------------------------
16 COUNT STOPKEY
15 REMOTE


Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
16 COUNT (STOPKEY)
15 REMOTE [GRAY]
SELECT "ID_DOC","BL_TEMPLATE","ID_CR","TS_CR","ID_UP","TS_UP",
"CD_CNTY","TX_IMAGE_NM","TX_PAGE","TX_FILE_NAME",
"TX_FORM_NAME","TX_TEMPLATE_NAME" FROM "DOC_TEMPLATE"
"DOC_TEMPLATE"

********************************************************************************



Tom Kyte
May 03, 2004 - 7:03 pm UTC

hmm, i've reproduced. Seems the query is materialized with the rownums and then selected from -- so it bombs out.

I'm not sure if that is "a bug or not". Smells a bit like one -- you might want to file that test case with support.

BUT

A reader, September 15, 2004 - 3:00 am UTC

but the result is not BLOB,
and,if I want to convert the result to LONG type,
what shall we do?
thanks!

Tom Kyte
September 15, 2004 - 9:21 am UTC

you won't. you'd have to get the blob and insert it via a client application into a long raw. it would not make sense.

Return Type

gaobo, September 16, 2004 - 11:03 pm UTC

My mean is:
what return-type is it when using 'dbms_lob.substr'?
BLOB or CLOB ?
Thanks your !

Tom Kyte
September 17, 2004 - 8:28 am UTC

varchar2 if the input was a clob
raw if the input was a blob


describe would have told you this
documentation would have too

SQL> desc dbms_lob

....
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        BLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOB_LOC                        CLOB                    IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
FUNCTION SUBSTR RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE_LOC                       BINARY FILE LOB         IN
 AMOUNT                         NUMBER(38)              IN     DEFAULT
 OFFSET                         NUMBER(38)              IN     DEFAULT
 

doubt

gaobo, September 19, 2004 - 10:15 am UTC

Oh,yes! Thanks very much! But,
the type(long raw) will be washed out in future,why ORACLE still use it? "dbms_lob.substr", Do I must use it ? If so, DO I must use to_lob function to convert the long raw type to the blob type ? Can I convert the long raw type to long type directely ?
Thanks!

Tom Kyte
September 19, 2004 - 10:49 am UTC

insufficient information to answer.

this is a quesiton about dblinks, is there a dblink involved here, what are your actual requirements, what is your actual goal.

goal

gaobo, September 19, 2004 - 11:36 pm UTC

How do you do,
I want to access a remote table(included a long field) using my program, but now, I find the table's field have been changed to blob type already, if I dont't modify my program, what can I do?
Thanks!

Tom Kyte
September 20, 2004 - 7:58 am UTC

a long field would have been text.

a blob field is binary data.

you have apples and oranges here.


If someone changes your schema, there is an excellent chance you will have to modify your program.

Thanks for your suggestions!

gaobo, September 20, 2004 - 8:21 pm UTC


How to do???!!!

A reader, April 21, 2005 - 5:43 pm UTC

Hi Tom,

I have the following situation:

I have 2 databases db1 and db2. db1 has a table 'a' with structure

create table a
(
id number,
img blob
);

db2 has a table 'b' with structure

create table b
(
id number,
img blob
) but img is always null in 'b'. Now I want to create a view v1 which should have the id from table 'b' and image from table 'a'. If I do

create view v1 as
select b.id, a.image@lnkdb1 img
from b, a
where a.id = b.id

I am getting ORA-22992: cannot use LOB locators selected from remote tables. How can I accomplish this? I read a suggestion earlier in this article and I tried to create a view on db1 with dbms_lob.substr(img) and then when I created the view the img column is coming as RAW but I want blob. Please help.

Thanks.

Tom Kyte
April 22, 2005 - 10:16 am UTC

you cannot. lob locators do not cross database links.

A reader, April 22, 2005 - 10:42 am UTC

Is there any other option I can try?



Tom Kyte
April 22, 2005 - 10:59 am UTC

not over a dblink.

if you are finding you are using a dblink for more than copying data over every now and again, you are finding you should have had a single database.

maryx.wagner@state.de.us, September 14, 2005 - 4:07 pm UTC


Thank you tom!

S, August 16, 2006 - 1:53 am UTC

Thank you tom!

Remote view

Keith Stein, October 05, 2009 - 4:18 pm UTC

Tom, I am try to use option #1 above by creating the view on the remote system and accessing that view using the DB link.
I have created the view but when I try to access it within a SELECT statement, I get a “ORA-00942: table or view does not exist” error. The view can be accessed on the remote sever so I know it does exist and is working, I just cannot see it from the local server using the db link.
Any suggestions? Thanks

Tom Kyte
October 08, 2009 - 6:53 am UTC

show us

cut and paste from sqlplus



select * from owner.view@remote;

select * from all_objects@remove where object_name = 'your view name';

A reader, November 13, 2009 - 11:40 am UTC

We have the same problem with selecting blob data across a db link. Someone here came up with a function that looks like it’s working.

Pusdo code:

function getblob() return blob is
b blob;
sqlStr varchar2(1000);
begin

sqlStr := ‘select theblob from thetable where id=123’;l
execute immediate sqlstr into b;
return b;

end;

then:
select getblob() from dual;




something from other thread that I find relevant...

Nirav, February 13, 2010 - 3:50 am UTC

This is in relation to the original question...I guess even today the situation is the same - we can't select lob over db links. But one other thing I noticed is that -one can insert or update the lob over db link!
There is a thread on OTN that shows a "workaround" for it:
http://forums.oracle.com/forums/thread.jspa?threadID=317043&start=0&tstart=0

Thanks,

Meta Immi, April 12, 2013 - 9:52 am UTC

Hello Tom,

I have a requirement to get a clob from a remote database(10g) to a local one(11g).
The clob has to undergo a conversion by a function from a cartridge installed on remote database (this is why i cant copy the clob first and process it locally).
I am also very limited to what i can do on the remote database.

I tried to go with a view(that performs the conversion) as suggested, however i receive:
"ORA-64202: remote temporary or abstract LOB locator is encountered", when i try to
insert as select from that_view@remote_site into a local table.


Forums suggest, that it would work if the local database was also 10g, but in 11g it does not work. Is this true? Is there some workaround?
Tom Kyte
April 22, 2013 - 3:05 pm UTC

I think you'll want to run a procedure that plops that lob into a scratch table on the remote site (run a remote procedure) and then select that modified lob over the dblink.

why can we insert but not select

Spur, May 01, 2013 - 2:54 pm UTC

Tom,

Could you please explain why can we insert clob over db link into a table but not select it?
Tom Kyte
May 06, 2013 - 3:42 pm UTC

when you select a lob in a read query, you get a lob locator, a pointer, you don't actually "get the data".

then you dereference the lob, you dereference the pointer.

and currently we only dereference "local" pointers, we don't walk a dblink back to get lob data.


but when you insert as select - we stream the data back - we do not get a lob locator (we wouldn't say a pointer in the new row), we get the actual data.

Artem, July 07, 2016 - 8:28 pm UTC

LOAD CLOB over database link
log_data - CLOB

merge into LOG_TEST d
using LOG_TEST@SP1GBT s
on (d.id = s.id)
when not matched then insert (
d.id, d.log_date, d.module, d.message, d.log_data)
values (
s.id, s.log_date, s.module, s.message, s.log_data)

Feedback

Ravinder, October 05, 2016 - 8:24 am UTC

Hi Tom ,

I have a similar situation where I am supposed to archive the data data to another schema in another system. I used DBLINKS. for my SIT purpose I used Normal Instances and a dblink between the same. My archival data worked without any issue. My workaround was using materialized views.

However when I moved the code the two RAC instances for purposes of UAT I was back to the same issue of cannot use LOB locators.

The databases were on Solaris Oracle 11.2.0.3.

Just to let you know.
October 06, 2016 - 12:51 am UTC

If you've got something that works on single instance, but does not work on RAC, then please give us a test case of that. That should never be the case

Temporary Table worked!

Gabriel B. Brito, October 30, 2018 - 7:59 pm UTC

This was the solution for me because i didn't have access to create a view in the remote database.

using from dual

marcelo, May 26, 2020 - 6:47 pm UTC

just used "select fiel_blob from table, dual"... very import add 'dual' table at the end clause!

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here