db link 사용시 lob타입 테이블 문제

Ora-22992 has a workaround in 10gR2 [ID 436707.1]

 

수정 날짜:2012. 1. 30유형:PROBLEM상태:PUBLISHED우선순위:3

 

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

When trying to select from a remote table, getting  'ORA-22992: cannot use LOB locators selected from remote tables.' as following:

1. From the remote database:
create user cris identified by cris;
grant connect, resource to cris;
conn cris/cris
create table test(id number, obj clob);
insert into test values(1,'sdfsdfsfd');
insert into test values(2, 'sdfsdfsfdvfgdfvgdfvdf');
commit;

2 From the local database:

create user cris identified by cris;
grant connect, resource, create database link to cris;
conn cris/cris
create database link torem using 'identification of the remote database in the tnsnames.ora';

set serveroutput on

declare
my_ad clob;
BEGIN
SELECT obj INTO my_ad FROM test@torem where id=1;
dbms_output.put_line(my_ad);
END;
/

*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at line 4

Changes

Cause

Trying to select LOB column from a table at a remote site using dblink.

Solution

The following link to documentation states that you can successfully select form lob objects through dblink All what you have to do is to receive the LOB objects into variables defined as CHAR or RAW.

Oracle� Database Application Developer's Guide - Large Objects  10g Release 2 (10.2)
Part Number B14249-01

Restrictions Removed in Oracle Database 10g Release 2 

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_what.htm#CHDGGBCG

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14249/adlob_data_interface.htm#CACIFCJF

Here are the steps of how to do that:

1. Selecting a CLOB object through the dblink:

 1.1 From the remote database:

create user cris identified by cris;
grant connect, resource to cris;
conn cris/cris
create table test(id number, obj clob);
insert into test values(1,'sdfsdfsfd');
insert into test values(2, 'sdfsdfsfdvfgdfvgdfvdf');
commit;

1.2 From the local database:

create user cris identified by cris;
grant connect, resource, create database link to cris;
conn cris/cris
create database link torem using 'identification of the remote database in the tnsnames.ora';

set serveroutput on

declare
my_ad varchar(6000);
BEGIN
SELECT obj INTO my_ad FROM test@torem where id=1;
dbms_output.put_line(my_ad);
END;
/

2. Selecting a BLOB object through the dblink:

 2.1 From the remote database:

create user cris identified by cris;
grant connect, resource to cris;
conn cris/cris
create table test2(id number, obj blob);
insert into test2 values(1,empty_blob());
insert into test2 values(2,empty_blob());
commit;

2.2 From the local database:

create user cris identified by cris;
grant connect, resource, create database link to cris;
conn cris/cris
create database link torem using 'identification of the remote database in the tnsnames.ora';

declare
my_ad raw(50);
BEGIN
SELECT obj INTO my_ad FROM test2@torem where id=1;
END;
/

References

NOTE:158924.1 - Referencing DBMS_LOB.COPY() Against Remote Table Fails With ORA-22992