|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
External LOBs (BFILEs), 4 of 41
DIRECTORY object facilitates administering access and usage of
BFILEs in an Oracle Server (see
DIRECTORY in Oracle9i SQL Reference). A
DIRECTORY specifies a logical alias name for a physical directory on the server's filesystem under which the file to be accessed is located. You can access a file in the server's filesystem only if granted the required access privilege on
DIRECTORY object also provides the flexibility to manage the locations of the files, instead of forcing you to hardcode the absolute pathnames of physical files in your applications. A
DIRECTORY alias is used in conjunction with the
BFILENAME() function, in SQL and PL/SQL, or the
OCILobFileSetName(), in OCI for initializing a
Oracle does not verify that the directory and pathname you specify actually exist. You should take care to specify a valid directory in your operating system. If your operating system uses case-sensitive pathnames, be sure you specify the directory in the correct format. There is no need to specify a terminating slash (for example,
To associate an operating system (OS) file to a
BFILE, first create a
DIRECTORY object which is an alias for the full pathname to the operating system file.
To associate existing operating system files with relevant database records of a particular table use Oracle SQL DML (Data Manipulation Language). For example:
INSERTto initialize a
BFILEcolumn to point to an existing file in the server's filesystem
UPDATEto change the reference target of the
NULLand then update it later to refer to an operating system file via the
OCILobFileSetName() to initialize a
BFILElocator variable that is then used in the
VALUESclause of an
The following statements associate the files
image2.gif with records having
key_value of 21 and 22 respectively. '
IMG' is a
DIRECTORY object that represents the physical directory under which
image2.gif are stored.
INSERT INTO Lob_table VALUES (21, BFILENAME('IMG', 'Image1.gif')); INSERT INTO Lob_table VALUES (22, BFILENAME('IMG', 'image2.gif'));
UPDATE statement below changes the target file to
image3.gif for the row with
BFILENAME() is a built-in function that is used to initialize the
BFILE column to point to the external file.
Once physical files are associated with records using SQL DML, subsequent read operations on the
BFILE can be performed using PL/SQL
DBMS_LOB package and OCI. However, these files are read-only when accessed through
BFILES, and so they cannot be updated or deleted through
As a consequence of the reference-based semantics for
BFILEs, it is possible to have multiple
BFILE columns in the same record or different records referring to the same file. For example, the
UPDATE statements below set the
BFILE column of the row with
key_value 21 in
lob_table to point to the same file as the row with
UPDATE lob_table SET f_lob = (SELECT f_lob FROM lob_table WHERE key_value = 22) WHERE key_value = 21;
Think of BFILENAME() in terms of initialization -- it can initialize the value for the following:
This has the following advantages:
BFILEis temporary, and scoped just within the module on which you are working, you can utilize the
BFILErelated APIs on the variable without ever having to associate this with a column in the database.
BFILEcolumn in a server side table, initialize this column value, and then retrieve this column value via a
SELECT, you save a round-trip to the server.
For more information, refer to the example given for
LOADFROMFILE (see "Loading a LOB with BFILE Data").
The OCI counterpart for
OCILobFileSetName(), which can be used in a similar fashion.
The naming convention for
DIRECTORY objects is the same as that for tables and indexes. That is, normal identifiers are interpreted in uppercase, but delimited identifiers are interpreted as is. For example, the following statement:
creates a directory object whose name is '
SCOTT_DIR' (in uppercase). But if a delimited identifier is used for the
DIRECTORY name, as shown in the following statement
the directory object's name is '
Mary_Dir'. Use '
SCOTT_DIR' and '
Mary_Dir' when calling
BFILENAME(). For example:
On WindowsNT, for example, the directory names are case-insensitive. Therefore the following two statements refer to the same directory: