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 126.96.36.199 :
— 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.
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
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 :
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
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; /
2 views last month, 2 views today