BLOB Via Db Link

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 EDITIONABLE VIEW  "VHRIMAGES" ("EMPID", "MYLOB") AS 

  SELECT EMPLOYEE_ID

       ,getblobviadblink('HR','SMFIMAGES.HRIMAGES','PICTURE',ROWID) MYLOB

FROM  SMFIMAGES.HRIMAGES@HR

/