EnterpriseDB: BFILE Oracle Tips by Burleson Consulting

Oracle supports a data type called a BFILE.  This binary file is stored in the external operating system file system.  Oracle stores a pointer to this file rather than storing the actual file in the database.  If for some reason you did not want to store your binary file in the database, this would be the way to do it.

EnterpriseDB does not support BFILE.

Large Object

In the NUMERIC data types section, I spoke about a special data type called an OID.  The OID can be used as an indicator.  If you create a column of OID type, you can use a special function called lo_import, which means Large Object Import, to associate a binary file with an OID.  There are other functions that operate on large objects that I discuss in the section on Binary SQL Functions below.

When you use large object support via the "lo" functions, you store all of your large objects in a common large object table.  I would recommend that you stick with BLOBs when you need to store binary large objects and TEXT when you need to store character large objects.  The "lo" functionality is available should you need it for some reason.

You could mimic BFILEs with this functionality.  You could encapsulate the BFILE style access in your own procedures and "load from file" or "save to file" by calling "lo" routines.  I will show an example of this in a later section.

For now, you can create a binary data types table and manipulate some data.

CREATE TABLE binary_table (
  my_binary BLOB,
  lob_binary OID ); 

INSERT INTO binary_table ( my_binary, lob_binary )
  VALUES ('\\000\\001\\002\\003\\000', lo_import('c:\\temp\\picture1.jpg') ); 

SELECT my_binary, lob_binary
  FROM binary_table; 

edb=# CREATE TABLE binary_table (
edb(#   my_binary BLOB,
edb(#   lob_binary OID );
CREATE TABLE
edb=#
edb=# INSERT INTO binary_table ( my_binary, lob_binary )
edb-#   VALUES ('\\000\\001\\002\\003\\000',
edb-#           lo_import('c:\\temp\\picture1.jpg') );
INSERT 0 1
edb=#
edb=# SELECT my_binary, lob_binary
edb-#   FROM binary_table; 

      my_binary       | lob_binary
----------------------+------------
 \000\001\002\003\000 |      16685

(1 row)

edb=#

The insert above will insert the picture, "picture1.jpg", which is in the c:\temp directory, into a common large object table in the database.  It will create an OID reference, which is stored in the lob_binary column of the binary_table table.

There are no synonyms that are equivalent to an OID in this instance.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.