Saving File in Oracle Database

Topics: Web server, File system, Database management system Pages: 2 (370 words) Published: November 2, 2010
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.

Suppose we have a table name ImageTable. The table should be like this. CREATE TABLE ImageTable

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;

delete from ImageTable where filename = p_filename;
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 fileName =...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Oracle Database: SQL Fundamentals Essay
  • Oracle Database 11 G Essay
  • Oracle Workflow Database Table Essay
  • Oracle Database Backup and Recovery Essay
  • Files and Relational Databases Essay
  • database Essay
  • Database Essay
  • Database Essay

Become a StudyMode Member

Sign Up - It's Free