Saving File in Oracle Database

Sometimes we have to save image, any type of multimedia file or text file in Oracle database from client. In web application client must upload the file in web server or any other machine. But web server can access that directory where the files are uploaded. To complete the operation we can use Oracle Blob data type, Oracle directory and with a procedure.

Implementation:
Suppose we have a table name ImageTable. The table should be like this.
CREATE TABLE ImageTable
(
ID NUMBER,
FILENAME VARCHAR2(3000 BYTE),
DATA BLOB,
CONSTRAINT ImageTable_ID_PK PRIMARY KEY (ID)
);

Here filename is unique. Also we have a sequence for the table name “imagetable_seq”. Suppose the files are uploaded in “C:\pics” directory in web server. If the web server and database server in the same machine then there is no problem for the database server to access that files to operate.
At first we should create a directory where the file are uploaded or stored like we stored in “C:\pics” in server machine. So our script should be like this: create or replace directory Image_FILES as ‘C:/pics’;
Note that here Oracle use forward slash. We create a directory in application server or database server. In our case both are same machine. So there is no problem to access file for the Oracle Database server. After that we should create a oracle procedure name “saveImage” which script should be like this:

create or replace procedure saveImage(p_filename varchar2,seq_Id out number) as l_clob blob; l_bfile bfile;

begin delete from ImageTable where filename = p_filename; commit; insert into ImageTable (ID,fileName,data) values (imagebytes_seq.NEXTVAL, p_filename, empty_blob()) returning data into l_clob; l_bfile := bfilename( ‘Image_FILES’, p_filename ); dbms_lob.fileopen( l_bfile,DBMS_LOB.LOB_READONLY ); dbms_lob.loadfromfile( l_clob, l_bfile,dbms_lob.getlength( l_bfile ) ); select imagebytes_seq.CurrVal into seq_Id from dual; update imagebytes set data =l_clob where