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