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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation