Ask the Expert

Storing image file in table

How can I store an image file such as a .jpg or .bmp in a table? If it is to be stored outside the database and called from SQL, how can it be done? Also tell me how should I do this in PL/SQL.

    Requires Free Membership to View

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.

This was first published in July 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: