How do I write an uploaded file to disk using the Oracle directory alias and BFILE objects?

Question

I have a java webapp which needs to upload files via http and then store them on the server. The files need to be associated with specific records in an Oracle database, so there will be a table in the database storing the reference to the associated record, and a reference to the file, along with other descriptive data such as title etc. The options for this table appear to be:

• store the file as a BLOB
• store a BFILE reference to the file
• store a String containing the path to the file

We would prefer to store the file outside of the database, so we will not store it as a BLOB. The DBAs have indicated that their preferred option is to store the reference as a BFILE. The oracle.sql.BFILE object provides access to an InputStream for reading the file, but no obvious way of writing to it.

What is the best way of writing the file data back to disk when the only reference to the storage directory is the Oracle directory alias?

---

We decided that simple java.io was the best way to write to the file, which means that the storage directory has to be available to the web application servers as a mount. Since the directory was available to the webapp anyway, we then decided that the BFILE was not required and just to store the filename in the database instead.

Answer 1

According to the Oracle JDBC Developers Guide - 14.6 Working with BFILEs:

• BFILEs are read-only. You cannot insert data or otherwise write to a BFILE.
• You cannot use JDBC to create a new BFILE. They are created only externally.

So it seems that you need a separate tool/method to upload the data to the Oracle storage directory, which probably makes BFILE a bad choice for your scenario.

Answer 2

"We would prefer to store the file outside of the database"

Why ? What is your backup/recovery scenarios for this. For example, in the event of a disk failure, how would you recover the file ? Are the files purely transitory, so you don't actually need to preserve them ?

Basically, the BFILE is a compromise between BLOB storage and simply storing a path in the database. You write to it in the same manner as you would a conventional file, then make it 'available' to the database for reading. If your web-app is running on a different physical server from the database, you'd need to have a storage location that is accessible to both boxes (and appropriate permissions for read/write).