Introduction

Oracle introduced the concepts of DIRECTORY objects in Oracle 8.0. DIRECTORY object is a logical structure that represents a physical directory on the server's file system. Initially the DIRECTORY object was used only in context of the "DBMS_LOB" PL/SQL package, for administering and accessing files located under the directory identified by the DIRECTORY object. However beginning with Oracle9i, the DIRECTORY object is now used in many other features of Oracle, such as EXTERNAL TABLE and PL/SQL UTL_FILE package.

Some features of the directory objects are:

1. DIRECTORY objects are owned by the SYS user, (even if another user creates the DIRECTORY)
2. DIRECTORY names are unique across the database, (because all the directories are located in a single namespace, namely SYS).
3. The DIRECTORY object permissions are NOT the same as OS permissions on the physical directory on the server file system.
4. Discrete database privileges cannot be granted to the files contained in the physical directory represented by the DIRECTORY object.

Advantages of a DIRECTORY Object

Using a DIRECTORY object has the following advantages:

1. In the use of UTL_FILE package, unlike in the earlier versions, we no longer need to specify the file system directory path in the init.ora file, (UTL_FILE_DIR parameter). Thus, you change the path dynamically without having to shutdown and restart the instance.

2. There is a higher level of security and granular control in administering applications that use UTL_FILE. For example, it is easier to maintain 5 DIRECTORY objects, each mapping to a particular physical directory on the file system, rather then having multiple entries for the UTL_FILE_DIR parameter in the init.ora file.

Let us explore how the DIRECTORY object can be used and what are advantages it provides.

First steps to set up a DIRECTORY object.

The first step for using a directory in Oracle is to create it. In addition, have the right permissions and privileges to do so.

In order to create a directory you, (the database user), should have the following privileges:

CREATE ANY DIRECTORY.

CREATE OR REPLACE DIRECTORY test_files AS E:\oracleWork;

By default, you do get the READ WRITE privileges on this object. However, if you wish to assign a READ WRITE privilege to another user you can GRANT the necessary privileges as follows:

GRANT READ ON DIRECTORY test_files TO PUBLIC;

Now we move on to using this directory object in various contexts as follows:

Page 2