[odb-users] Storing files in an oracle database

[Boris Kolpackov]

Hi Marcel,

Marcel Nehring <mne at qosmotec.com> writes:

> > Skimming through the OCI docs, BFILE appears to be special in that on
> > INSERT or UPDATE you specify the file name, not its data. Not sure what
> > SELECT returns...
> 
> It returns a locator similar to a SELECT on a BLOB column.

Ok, so it should be possible to provide proper support for BFILE in
libodb-oracle. The dual mapping might be tricky though.


> To me it seems that it is not possible to cast BFILE to BLOB. Although
> both data types behave very similar when reading from the database, a
> simple cast doesn't seem to help. I am getting an ORA-00932 error.

It seems one could create a function that does the conversion:

http://stackoverflow.com/questions/12263816/function-in-pl-sql-for-reading-bfile-into-blob-dont-show-the-result

And you can use such functions in 'db map' pragmas as shown in the
oracle/custom test in the odb-tests package. It's possible this defeats
the whole purpose of using BFILE in the first place. But it would be
interesting to know if it actually works. Could you give it a try?


> My basic idea this time was to split my internal data members into
> two. One for the filename and one for the file contents.

You could probably almost do it with sections except that ODB will
always persist both members.

What could works is encoding the file name as BLOB. Then you would
write another function, blob_to_bfile(), except instead of the binary
data blob will contain the file name. Given these two functions:

#pragma db map type("BFILE") as("BLOB")   \
               to("blob_to_bfile((?))")   \
               from("bfile_to_blob((?))")

Boris