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.