Exploring the use of Directory Objects in Oracle 9i

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.