Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 22, 2003

Exploring the use of Directory Objects in Oracle 9i

By DatabaseJournal.com Staff


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.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM