Analytics Software

Hi,

It's well explained in the documentation:

http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_ddl.htm#g1012364

Edited by: Manguilibè KAO on 6 juin 2012 22:31

Use the TO_LOB function. See:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions205.htm#SQLRF06134

You can find more examples if you do a search on http://www.oracle.com/pls/db112/homepage
or http://asktom.oracle.com

here is the example..

CREATE TABLE lob_table (id NUMBER, doc BLOB);
INSERT INTO lob_table VALUES (1, EMPTY_BLOB()); 

DECLARE
  src_lob  BFILE := BFILENAME('MY_DIR', '/tmp/me.gif');
  dest_lob BLOB;
BEGIN
  INSERT INTO lob_table VALUES(2, EMPTY_BLOB())
     RETURNING doc INTO dest_lob;

  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                         SRC_LOB  => src_lob,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);

  COMMIT;
END;
/

Hi,

Step to Follow :

1. Create a directory object pointing to a physical directory on the database server that the "oracle" user has read/write permissions on.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY IMAGES ost/';
GRANT READ, WRITE ON DIRECTORY images TO db_user_name;

2. Procedure to Load the BLOB File.

DECLARE
  l_dir    VARCHAR2(10) := 'IMAGES'; -------Is the Directory Object Created Above.
  l_file   VARCHAR2(20) := 'site_logo.gif'; ------ Is the BLOB File that is present in the Directory mentioned.
  l_bfile  BFILE;
  l_blob   BLOB;
BEGIN
  INSERT INTO images (id, name, image)
  VALUES (1,l_file, empty_blob())
  RETURN image INTO l_blob;

  l_bfile := BFILENAME(l_dir, l_file);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  COMMIT;
END;

Thanks,
Shankar