In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 - Release: 9.2 to 11.1
Information in this document applies to any platform.
Checked for currency on 21 Oct 2010.

Symptoms

This article provides a workaround for error ORA-22992 which occurs when selecting LOB data over database links  for example

SQL> select text from Table@DBlink;
select text from Table@DBlink
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

<>The following test case should explain more details about the issue, it assumes that we have two databases A, B both are 9.2.0.8 :


-- On the target, instance B in our example : 

conn user/pass 
create table tab1(col1 number, col2 clob); 
insert into tab1 values(101,'This is test data'); 
insert into tab1 values(102, 'aims to test the LOB'); 
commit;  

-- Now on the local instance, A : 

conn user/pass 
create database link link_to_B connect to user  
identified by pass using 'B'; -- B is the TNS alias of Database B 

select * from test@torem; 

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

--The same issue will occur even if we have used PLSQL block : 

set serveroutput on 

declare 
var1 varchar(6000); 
BEGIN 
SELECT col2 INTO var1 FROM tab1@link_B where col1=101; 
dbms_output.put_line(var1); 
END; 
/  
ERROR at line 4: 
ORA-22992: cannot use LOB locators selected from remote tables 

Cause

Selecting LOB data over database link is not permitted pre-10g release 2.
On 10.2 and later, it can be fetched into PLSQL variable from the appropriate type.
The following part contains more details about how to manipulate the LOB data in both 10.2 and earlier.

Solution

On 9i and 10g release one :

The workaround is not to select LOB data over the database link, but instead convert it on the remote site first.

Solution one:

--On remote (B) :

create or replace function lobconverter(lobin in clob) return varchar2 is
  Result varchar2(5000);
begin
  result:=lobin;
  return(Result);
end lobconverter;

-- Now we need to ensure that this function is being called on the remote site to achieve that we can simply create a view like the following :

create view testv as select col,lobconverter(col2) col2 from tab1;

-- on the local database A :

-- Instead of selecting from the table, simply select from the view :

select * from tab1@link_to_b;

 COL1
----------
COL2
--------------------
         101
This is test data

         102
aims to test the LOB

-Solution 2

-- On database B create procedure to run the select statement :

CREATE OR REPLACE PROCEDURE lobconverterproc IS
        var1        varchar2(1000);
    BEGIN
        SELECT col2 INTO var1 FROM tab1
            WHERE rownum <2;

-- you can add different condition,
-- or even you can add loob to select all the data in the table.

        DBMS_OUTPUT.PUT_LINE('Col2: ' || var1);
   END;
 /

--Then on the local database call the procedure as the follwoing :

SQL> set serveroutput on ;
SQL> exec lobconverterproc @Link_to_B ;

Col2: This is test data

- Solution 3:

Create the function on the target as before and create synonym for it on the local as follows:

-- On target B:
-- 1. create the same lobconverter function as before.

-- On local A:
create synonym lobconverter for lobconverter@Link_to_B;

-- now just select :
SQL> select col1, lobconverter(col2) from test@link_to_B;

 COL1
----------
COL2
--------------------
         101
This is test data

         102
aims to test the LOB

The above examples provides three different solutions  which depends on the same concept, convert to varchar2 on the remote side first.


The following part shows how we can achieve the same target using the new feature available on 10.2 :

10g release 2 :

The following documentation shows how exactly the restriction of LOBs over db links has been removed. Click here

With same testcase scenario as above on 10gr2  we need to execute the following without creating any objects on B :

-- On A :

declare
var1 varchar(5000);
BEGIN
SELECT col2 INTO var1 FROM tab1@link_to_b where col2=101;
dbms_output.put_line(my_ad);
END;
/ 

The above examples could be combined in any other structures as per customer's requirement

Note :
All the above examples assumes that the LOB data is CLOB, the same concepts are valid in case of BLOB.
The only difference is that we will need a variable with data type RAW to carry the LOB data .
After the conversion with CLOB  we could use  VARCHAR2 like the following :

declare
var2 raw(50);
BEGIN
SELECT col2 INTO var2 FROM tab2@link_to_b where id=101;
---> Tab2 is the same structure like tab1 but with BLOB instead of the CLOB.
END;
/

References

NOTE:158924.1 - Referencing DBMS_LOB.COPY() Against Remote Table Fails With ORA-22992
NOTE:436707.1 - Ora-22992 has a workaround in 10gR2