 |
|
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.
