Exploring the use of Directory Objects in Oracle 9i
January 22, 2003
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:
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
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
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:
Now we move on to using this directory object in various contexts as follows.