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

I got ” ORA-22992: cannot use LOB locators selected from remote tables ”  error in Oracle database.

 

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

 

Details of error are as follows.

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

Cause: A remote LOB column cannot be referenced.

Action: Remove references to LOBs in remote tables.
SQL> select text from [email protected];
select text from [email protected]
*
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

 

 

cannot use LOB locators selected from remote tables

This ORA-22992 errors are related with the remote LOB column cannot be referenced.

Remove references to LOBs in remote tables.

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

— 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 [email protected]; 

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 [email protected]_B where col1=101; 
dbms_output.put_line(var1); 
END; 
/  
ERROR at line 4: 
ORA-22992: cannot use LOB locators selected from remote tables 


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 [email protected]_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 [email protected]_to_B;

-- now just select :
SQL> select col1, lobconverter(col2) from [email protected]_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 :

10gR2 to 12cR1:

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 [email protected]_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 [email protected]_to_b where id=101;
---> Tab2 is the same structure like tab1 but with BLOB instead of the CLOB.
END;
/

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 384 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply