How to store a file in Oracle DB ?

[Oliv63]

Hi !

I need to strore the content of a file in my database but I don't know how to do. I use Oracle8i and Java(JDBC). The files I would like to store can be large (more than 1Mo sometimes).
What type of field do I need to create in my table ?
And how to store the content of these files ?
(I try to read them and get their content into a String but I can't insert this String in my DB because the String is too long)

Have you have any idea ?
Thanx for your help.

Oliv'

 

[rajarajan]

Hi Oliv63,
Let some DB expert answer your question.
If you don't get one, you can try this out.
You can store all your files in a specific directory, and store just the name and location of the file in the DB. This way you can achieve what you want, bypassing all the limitations of the database.
Hope this helps.

Regards,
Rajarajan

 

[dendenners]

I don't know too much about this, but I've seen it done using an oracle BLOB (binary large object) data type. I think what you do is firstly create an empty blob in the new entry in your table. Then open an OutputStream to the blob and start writing your data to this OutputStream with an InputStream from the file you want to copy

to get the correct outputstream, use code similar to this:

//CONTENT is the blob field in the dbase
ResultSet fileDetails = stmt.executeQuery("select CONTENT from FILE_TRANSFER where file_id = " + fileId + " for update"
//get the blob from the resultset
BLOB blob = ((OracleResultSet)fileDetails).getBLOB(1);
//open the outputstream to the blob
OutputStream out = blob.getBinaryOutputStream();


initally when creating the record, put 'empty_blob()' into the blob field.

Hope this helps