This article explores the advantages of Oracle9i's Directory Object. Learn how to
set up a directory object, access data using external tables, and
get directory information from the database.
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:
- DIRECTORY objects are owned by the SYS user, (even if another user creates the DIRECTORY)
- DIRECTORY names are unique across the database, (because all the directories are located in a single namespace, namely SYS).
- The DIRECTORY object permissions are NOT the same as OS permissions on the physical directory on the server file system.
- 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
UTL_FILE_DIR parameter). Thus, you change the path dynamically without having to shutdown and restart
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:
GRANT READ ON DIRECTORY
test_files TO PUBLIC;
Now we move on to using
this directory object in various contexts as follows.
To access data using EXTERNAL TABLES.
Consider a simple scenario in which you wish to access data
in flat files without actually loading them into the database. In order to facilitate
this load, Oracle9i provides a new feature EXTERNAL TABLES. In brief, the
external tables are READ ONLY tables and used to access external data as if it
were in a table in the database.
The flat files that contain the data are located in
a physical directory, identified by the external tables using the DIRECTORY
object. Using the directory object prevents unauthorized READ WRITE access to
the OS files, (data or log files), by the database user.
Consider the following data in a file "emp_load.dat"
This file emp_load.dat
should be located in the physical directory "E:\oracleWork" identified
by the directory object TEST_FILES which was created above.
The DDL to create the
external table will be as follows:
CREATE TABLE emp_external
( emp_id NUMBER(4)
, ename VARCHAR2(12)
, job VARCHAR2(12)
, mgr_id NUMBER(4)
, hiredate DATE
, salary NUMBER(8)
, comm NUMBER(8)
, dept_id NUMBER(2))
DEFAULT DIRECTORY TEST_FILES
ACCESS PARAMETERS (records delimited BY newline
fields terminated BY ',')
To view the data, all you
have to do is query the table like a regular table viz:
SELECT * FROM emp_external;
To provide OS file access to applications using UTL_FILE PL/SQL package.
To illustrate the use of
directory objects, consider the simple code that renames the file
emp_load.dat to a new file name
Note: TEST_FILES is the
directory object that specifies the file location. For simplicity, the new file
employee_records.dat is "moved" in the same physical directory
identified by TEST_FILES directory object.
/* UTL_FILE.FRENAME (
location IN VARCHAR2,
filename IN VARCHAR2,
dest_dir IN VARCHAR2,
dest_file IN VARCHAR2,
overwrite IN BOOLEAN DEFAULT FALSE);
UTL_FILE.FRENAME('TEST_FILES','emp1.dat', 'TEST_FILES', 'employee_records.dat');
WHEN OTHERS THEN
Note that the directory
object name, in context of using it in UTL_FILE package, is case-sensitive.
However, it is not case-sensitive when used to specify file location
in EXTERNAL TABLES.
If you change the directory
object name to "Test_files" in the above procedure code, then you will
get the error:
ORA-29280: invalid directory path.
In addition, all the rules
that applied for
UTL_FILE_DIR parameter settings hold
good for the DIRECTORY
object. For example, if we need to rename and move/store the file in a physical
directory "E:\oracleWork\data," then the above code cannot
be modified to reflect the destination location as "TEST_FILES\data" or
to "E:\oracleWork\data". Doing so will give the error:
ORA-29292: file rename operation failed
Thus in order to store the
file in the physical directory "E:\oracleWork\data,"
you will need to create a new DIRECTORY that maps to the location "E:\oracleWork\data".
Use of directory objects
also presents considerable flexibility in programming using UTL_FILE package.
Getting DIRECTORY information from the database
Query the following dictionary views to get information on
directories in the database and to know which directories are accessible to
ALL_DIRECTORIES - all directories accessible to the database user
ALL_TAB_PRIVS - for
verification whether you have the required READ/WRITE privilege on the
Additionally, all the rules that applied for UTL_FILE_DIR
parameter settings hold true for the DIRECTORY object. For example, if we need
to rename or move/store the file, (or simply write to the file), in a physical
directory "E:\oracleWork\data", then the above code cannot be modified to reflect
the destination location as "TEST_FILES\data" or to "E:\oracleWork\data." Doing
so will give the following error:
ORA-29292: file rename operation failed
This concludes our look
at Exploring the use of Directory Objects in Oracle 9i. We've seen what the
Advantages of a DIRECTORY Object are, how to set up a DIRECTORY object,
accessing data using EXTERNAL TABLES, and Getting DIRECTORY information from