PROBLEM:
-----------------
ORA-22992: cannot use LOB locators selected from remote tables
You can insert to a remote table that has LOB column, but can not select that column.
There are two solutions:
SOLUTION 1:
--------------------
create or replace function GETBLOBVIADBLINK
( dblnk in varchar2
,tbl in varchar2
,col in varchar2
,rwid in urowid)
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 rowid=:rwid' into tmplen using rwid;
-- precalc
tmpchk:=floor(tmplen/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 rowid=:rwid' into tmpraw using rwid;
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 rowid=:rwid' into tmpraw using rwid;
dbms_lob.append(retval,tmpraw);
end if;
return retval;
end;
/
CREATE OR REPLACE FORCE VIEW TESTVW1 (ID, MYLOB) AS
SELECT id
,getblobviadblink('ARCHIV','MYLOBTABLE','MYLOB',rowid) MYLOB
FROM MYLOB@archiv;
SOLUTION 2
------------------
create global temporary table tmplob (tmplob blob) ON COMMIT PRESERVE ROWS;
create or replace function getblobviadblink2
( dblnk in varchar2
,tbl in varchar2
,col in varchar2
,rwid in urowid)
return blob
is
PRAGMA AUTONOMOUS_TRANSACTION;
retval blob;
begin
execute immediate 'insert /*+ NOLOGGING */ into tmplob select '||col||' from '||tbl||'@'||dblnk||' where rowid=:rwid' using rwid;
select tmplob into retval from tmplob;
delete /*+ NOLOGGING */ from tmplob;
commit;
return retval;
end;
/
