Search code examples
.netoracle-databasebinaryfiles

What's the best way to save and retrieve binary files with Oracle 10g?

I'm about to implement a feature in our application that allows the user to 'upload' a PDF or Microsoft PowerPoint document, which the application will then make available to other users in a viewer (so they don't get to 'download' it in the 'Save as..' sense).

I already know how to save and retrieve arbitrary binary information in database columns, but as this will be a commonly used feature of our application I fear that solution would lead to enormously large database tables (as we know one of our customers will want to put video in PowerPoint documents).

I know there's a way to create a 'directory' object in Oracle, but is there a way to use this feature to store and retrieve binary files saved elsewhere on the Database Server?

Or am I being overly paranoid about the database size?

(for completeness our application is .Net WinForms using CoreLab / DevArt OraDirect.Net drivers to Oracle 10g)

Solution

  • Couple of options: You could put the BLOB column in its own tablespace, with its own storage characteristics; you could store the BLOBs in their own table, linked to the other table by an ID column. In either case as you suggested you could define the column as a BFILE which means the actual file is stored externally from the database in a directory. What might be a concern there is that BFILE LOBs do not participate in transactions and are not recoverable with the rest of the database.

    This is all discussed in the Oracle 10gR2 SQL reference, chapter 2, starting on page 23.