Storing image file in table

Before you can create and execute a procedure to load binary data from the file system into an Oracle table, you will need a table with a blob column and an Oracle directory pointing to the location on the file system, where the binary files are stored. Below I have examples of how to create an Oracle directory, a table with a blob column and a procedure to load binary files:

      /*-- Create the Directory ------*/
CREATE OR REPLACE DIRECTORY 
BLOB_FILE AS 'D:\BFILE';

Where "d:\bfile" is the location of the binary files.

--
-- DEMO  (Table) 
--
CREATE TABLE DEMO
(
   ID       INTEGER,
  THEBLOG  BLOB
)
TABLESPACE TS_BLOB
 
LOB (THEBLOG) STORE AS 
       ( TABLESPACE  CODE_TABLE_DATA 
          ENABLE      STORAGE IN ROW
         CHUNK       8192
          PCTVERSION  10
         NOCACHE
       );

For more information on the parameters for the lob data type, see Chapter 16 in "Oracle Database 10g PL/SQL Programming."

Below I inserted a .jpg file type file called: "01-New VST Retail Sales.jpg" into my DEMO table.

/* *********create the procedure LOAD_TABLE *************/

CREATE OR REPLACE procedure load_table 
as
       l_blob blob;
    l_bfile bfile;
begin
   insert into demo values
   ( 100, empty_blob(),'01-New VST Retail Sales.jpg',null,'jpg', null)
   returning theblob into l_blob;
   l_bfile := bfilename('BLOB_FILE ','01-New VST Retail Sales.jpg');
   dbms_lob.fileopen(l_bfile);
   dbms_lob.loadfromfile(l_blob, l_bfile,
   dbms_lob.getlength( l_bfile));
   dbms_lob.fileclose(l_bfile);
end;
/

Look here for more information on DBMS_LOB for Oracle 9i.