Exploring the use of Directory Objects in Oracle 9i - Page 2

January 22, 2003

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)) 
  ORGANIZATION EXTERNAL
  (TYPE oracle_loader
   DEFAULT DIRECTORY TEST_FILES         
   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.

DECLARE
BEGIN
/* 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');
 EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(substr(sqlcode,1,100));
 END;

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.