Exploring the use of Directory Objects in Oracle 9i

Wednesday Jan 22nd 2003 by DatabaseJournal.com Staff

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:

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

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)) 
  (TYPE oracle_loader
   ACCESS PARAMETERS (records delimited BY newline        
                      fields terminated BY ',')        
				LOCATION ('emp_load.dat')

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 employee_records.dat.

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.

   location  IN VARCHAR2,
   filename  IN VARCHAR2, 
   dest_dir  IN VARCHAR2,
   dest_file IN VARCHAR2,
UTL_FILE.FRENAME('TEST_FILES','emp1.dat', 'TEST_FILES', 'employee_records.dat');

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

ALL_DIRECTORIES - all directories accessible to the database user

ALL_TAB_PRIVS - for verification whether you have the required READ/WRITE privilege on the DIRECTORY object.

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 the database.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved