Directory Object

The DIRECTORY object facilitates administering access and usage of BFILEs in an Oracle Server (see CREATE DIRECTORY in Oracle8i 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.

Initializing a BFILE Locator

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

Note: 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 (e.g., /tmp/ is not necessary, simply use /tmp).

How to Associate Operating System Files with Database Records

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:

• Use INSERT to initialize a BFILE column to point to an existing file in the server's filesystem

• Use UPDATE to change the reference target of the BFILE

• Initialize a BFILE to NULL and then update it later to refer to an operating system file via the BFILENAME() function.

• OCI users can also use OCILobFileSetName() to initialize a BFILE locator variable that is then used in the VALUES clause of an INSERT statement.

Examples

The following statements associate the files Image1.gif and image2.gif with records having key_value of 21 and 22 respectively. 'IMG' is a DIRECTORY object that represents the physical directory under which Image1.dif and image2.dif are stored.

Note: You may need to set up data structures similar to the following for certain examples to work: CREATE TABLE Lob_table ( Key_value NUMBER NOT NULL, F_lob BFILE)

   INSERT INTO Lob_table VALUES
      (21,  BFILENAME('IMG', 'Image1.gif'));
    INSERT INTO Lob_table VALUES
      (22, BFILENAME('IMG', 'image2.gif'));

The UPDATE statement below changes the target file to image3.gif for the row with key_value 22.

   UPDATE Lob_table SET f_lob = BFILENAME('IMG', 'image3.gif') 
       WHERE Key_value = 22;

BFILENAME() and Initialization

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

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

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:

• BFILE column

• BFILE (automatic) variable declared inside a PL/SQL module

Advantages.

This has the following advantages:

• If your need for a particular BFILE is temporary, and scoped just within the module on which you are working, you can utilize the BFILE related APIs on the variable without ever having to associate this with a column in the database.

• Since you are not forced to create a BFILE column 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 DBMS_LOB.LOADFROMFILE (see "Load a LOB with BFILE Data").

The OCI counterpart for BFILENAME() is OCILobFileSetName(), which can be used in a similar fashion.

DIRECTORY Name Specification

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:

CREATE DIRECTORY scott_dir AS '/usr/home/scott';

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

CREATE DIRECTORY "Mary_Dir" AS '/usr/home/mary';

the directory object's name is 'Mary_Dir'. Use 'SCOTT_DIR' and 'Mary_Dir' when calling BFILENAME(). For example:

BFILENAME('SCOTT_DIR', 'afile')
BFILENAME('Mary_Dir', 'afile')

On WindowsNT Platforms

On WindowsNT, for example, the directory names are case-insensitive. Therefore the following two statements refer to the same directory:

CREATE DIRECTORY "big_cap_dir" AS "g:\data\source";

CREATE DIRECTORY "small_cap_dir" AS "G:\DATA\SOURCE";