views:

144

answers:

1

I have a java web application which needs to upload files and we want to store these files on the filesystem rather than in the database. The database will store the document metadata only.

The question is whether to just store the path as a string in Oracle, or as a BFILE locator? Can a BFILE locator point to a location which is on a different filesystem? If we have to store the file on the Oracle filer then we may as well just store it as a BLOB.

+2  A: 

The BFILE locator must be a directory that is visible from the Oracle server and which the Oracle OS user has read and write privileges on. So, while it could theoretically be a remote server the admin of that approach would get rather gnarly rather quickly. In practical terms it makes more sense to have the BFILE directory on the database server.

So might you just as well store it as a BLOB? Well, if the file is only ever going to be accessed through the database then, yes, definitely. Using a BLOB datatype has the advantage of transactional support. And in a related point, it makes backup and recovery simpler, because the file contents are guaranteed to be in sync with the database. This is not necessarily true of BFILEs, and almost certainly not true of BFILEs which reside on a different physical server.

APC