Re: Inserting BLOBs from files with the filename stored in another column

Hi, thanks for the reply; I think I figured it out now. I first declare a procedure:

CREATE OR REPLACE DIRECTORY
    BLOB_DIR
    AS
    'c:\temp';

CREATE OR REPLACE PROCEDURE BLOB_LOAD
(filename IN varchar2, filecontent OUT NOCOPY blob) AS

    tmpBlob blob;
    lFile BFILE := BFILENAME('BLOB_DIR', filename); BEGIN
    dbms_lob.createtemporary(tmpblob, TRUE);     

    DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);

    DBMS_LOB.OPEN(tmpBlob, DBMS_LOB.LOB_READWRITE);

    DBMS_LOB.LOADFROMFILE(DEST_LOB => tmpBlob,
                          SRC_LOB  => lFile,
                          AMOUNT   => DBMS_LOB.GETLENGTH(lFile));

    DBMS_LOB.CLOSE(lFile);
    DBMS_LOB.CLOSE(tmpBlob);

    filecontent := tmpBlob;
END; Then I call it using a cursor:

declare
  cursor tblCursor is
    select * from articles;
begin
  for tblRec in tblCursor loop
    BLOB_LOAD(tblRec.img_filename, tblRec.image);     update articles set image = tblRec.image where img_filename = tblRec.img_filename;   end loop;
  commit;
end;

I don't know, if this is good style, but it seems to work. Sorry for the trouble, regards,
Leo Received on Fri Oct 26 2012 - 12:32:14 CEST