Custom Function for accessing lob column at remote database link

Symptom:
When I select a table at remote database with lob data,

select remark2 from salesorder@dblink;

it shows the following error


ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.
Error at Line: 23 Column: 8


I surf the web, get the idea using temporary file.  So I create a custom function for facilitate similar queries.

Solution:  3 steps to get it solve


1. Creating a global temp table at your local database for cache remote db lob data

create global temporary table clob_cache 
( lobtable varchar2(200), 
lobfield varchar2(200), 
lobkey varchar2(200), 
lobdata clob ) 
on commit preserve rows;

2.  Create a function at local database for access the remote lob data

create or replace function fn_remote_clob
(
  p_table in varchar2,
  p_field in varchar2,
  p_rowid in varchar2
) return clob
as
  pragma autonomous_transaction;
  l_tempclob clob;
  l_sql varchar2(4000);
begin
    delete from clob_cache ;
    l_sql := 'insert into clob_cache (lobtable, lobfield, lobkey, lobdata) select '''||p_table||''','''||p_field||''','''||p_rowid||''','||p_field||' from '||p_table||' where rowid = '''||p_rowid||'''';
    EXECUTE IMMEDIATE l_sql;
    l_tempclob := null;
    select lobdata into l_tempclob from clob_cache
      where lobtable = p_table and lobfield= p_field and lobkey=p_rowid;
    commit;
    return l_tempclob;
end fn_remote_clob;


3. Select the data with function

select fn_remote_clob('salesorder@dblink','remark2',ROWID) as remark2
from salesorder@dblink;


The above is old method, which might create error ORA-01555 due to transaction processing issue. The following is new method.

New Solution: Two steps to get it solve

1. create function for CLOB
create or replace function FN_REMOTE_CLOB
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,wherecond in varchar2)
return clob
is
  retval clob;
  tmpraw varchar2(4000);  
  tmplen number;
  tmpchk number;
  chksize number;
begin
  --preset vars
  chksize:=2000;
  dbms_lob.createtemporary (retval,true);
  execute immediate 'select dbms_lob.getlength@'||dblnk||' ('||col||') from '||tbl||'@'||dblnk||' where '||wherecond into tmplen;

  -- precalc  
  tmpchk:=floor(nvl(tmplen, 0)/chksize);

  -- applicate frist chunks  
  for i in 0 .. tmpchk-1
  loop  
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||chksize||','||((i*chksize)+1)||') from '||tbl||'@'||dblnk||' where '||wherecond into tmpraw;
    dbms_lob.append(retval,tmpraw);
  end loop;

  -- applicate last entry
  if (tmplen-(tmpchk*chksize)) > 0 then
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||(tmplen-(tmpchk*chksize))||','||((tmpchk*chksize)+1)||') from '||tbl||'@'||dblnk||' where '||wherecond into tmpraw;
    dbms_lob.append(retval,tmpraw);
  end if;
  return retval;
end FN_REMOTE_CLOB;


2. Select the table with created function

select a.scno, FN_REMOTE_CLOB('DBLINK',SALESORDER', 'REMARK2', 'ROWID='''||a.ROWID||'''') from SALESORDER@DBLINK a;

or define your condition when ROWID is not applicable for VIEW instead of table

select a.scno, FN_REMOTE_CLOB('DBLINK',SALESORDER', 'REMARK2', 'SCNO='''||a.SCNO||'''') from VIEW_SALESORDER@DBLINK a;

p.s. the following function is for handling BLOB

create or replace function FN_REMOTE_BLOB
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,wherecond in varchar2)
return blob
is
  retval blob;
  tmpraw raw(2000);  
  tmplen number;
  tmpchk number;
  chksize number;
begin
  --preset vars
  chksize:=2000;
  dbms_lob.createtemporary (retval,true);
  execute immediate 'select dbms_lob.getlength@'||dblnk||' ('||col||') from '||tbl||'@'||dblnk||' where '||wherecond into tmplen;

  -- precalc  
  tmpchk:=floor(nvl(tmplen, 0)/chksize);

  -- applicate frist chunks  
  for i in 0 .. tmpchk-1
  loop  
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||chksize||','||((i*chksize)+1)||') from '||tbl||'@'||dblnk||' where '||wherecond into tmpraw;
    dbms_lob.append(retval,tmpraw);
  end loop;

  -- applicate last entry
  if (tmplen-(tmpchk*chksize)) > 0 then
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||(tmplen-(tmpchk*chksize))||','||((tmpchk*chksize)+1)||') from '||tbl||'@'||dblnk||' where '||wherecond into tmpraw;
    dbms_lob.append(retval,tmpraw);
  end if;
  return retval;
end FN_REMOTE_BLOB;





That's it, hope no more guy suffering from similar problem.  Have a good day.