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:

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

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

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 :

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 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

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;
/