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 last published in July 2006

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close