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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.