Workaround for ORA-22992 (Doc ID 796282.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - 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.



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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms