BVSync. 景川頁

透過dblink去select遠端資料庫中的table,
若遇到clob欄位時,下select語法後,
就會丟出這個訊息ORA-22992: cannot use LOB locators selected from remote tables.

若要避免這個問題,可使用一些方法來處理.
用一個小範例來測試:

[遠端資料庫 TESTA  DBLINK:D0A]
  1.建立convert function
   create or replace
     function democonvert(lobin in clob) return varchar2 is
      result varchar2(32767);
     begin
       result:=lobin;
       return(Result);
     end;
  
   2.建立測試的TABLE及寫入資料
     create table xxdemo
     (xxid number,
      xxclob clob);
    insert into xxdemo values(1,'abc');
    insert into xxtest values(2,'test');
    commit;

   3.可建立成view
   create or replace view xxdemo_v as
    select xxid,democonvert(xxclob) xxclob from xxdemo

[目地端資料庫 TESTB]
   1.建立synonym
   create or replace synonym democonvert for democonvert@D0A

  
   2.接下來你可以有多種方式來處理
     *直接select
      select xx.xxid,democonvert(xxclob) as democol from xxdemo@D0A xx;

     *使用用plsql,insert到目的端的table.
      建立資料表格  
     create table xxdemo_local
      (
       xxid number,
       xxclob clob
      );

     declare
       cursor aa_cur is
         select xx.xxid,democonvert(xxclob) as dd from xxdemo@D0A xx;
        begin
          for aa_rec in aa_cur
            loop
               insert into xxdemo_local(xxid,xxclob)
               values(aa_rec.xxid,aa_rec.dd);
            end loop;
        end;
  
     *使用view來insert
      insert into xxtest_sit(xxid,xxclob)
         select xxid,xxclob from xxdemo_v@D0A;

對於此狀況,有多種處理方式,可視情況處理之!