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.