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

/

Load Image From Directory to Database

 Load Image From Directory to Database

CREATE OR REPLACE PROCEDURE SMFIMAGES.LOAD_IMAGES(EMP number) IS
l_bfile bfile;
l_blob blob;
l_dest_offset integer := 1;
l_src_offset integer := 1;
l_lobmaxsize constant integer := DBMS_LOB.LOBMAXSIZE;
BEGIN
INSERT INTO CITIES (PIC_ID,CITY_NAME,CITY_IMAGE)
VALUES(emp,'test',EMPTY_BLOB())
RETURN CITY_IMAGE INTO l_blob;
l_bfile := bfilename('DB_IMG',emp||'.jpg');
dbms_lob.fileopen(l_bfile,dbms_lob.file_readonly);
dbms_lob.loadfromfile(l_blob,l_bfile,l_lobmaxsize,l_dest_offset,l_src_offset);
dbms_lob.fileclose(l_bfile);
--dbms_output.put_line('size of image is : '||dbms_lob.getlength(l_blob));
commit;
update hrimages set picture = l_blob where employee_id = emp;
delete from cities;
commit;
end;
/