Q

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.

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.
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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close