UTL_FILE

UTL_FILE is an oracle supplied built in package, which can be used for text file operations located within a file system. The file system can exist either on the server side or on the client machine.

The files accessed or operated by UTL_FILE are clear text files and not the binary files due to its incapability to handle the special characters.

File Access Philosophy

UTL_FILE cannot directly access any of the files located on the operating systems, but this is made possible through database directories. The DBA creates the server side directory for the file, on which the user must possess read and write access before using it in UTL_FILE operation.

If the target file location is on the server machine operating system, UTL_FILE has no access restrictions. But if the file location is a client machine, UTL_FILE has access only to those locations, which are shared and accessible from the server.

Prior to Oracle 10g, UTL_FILE operations used to be dependent upon a parameter UTL_FILE_DIR. All the directories to be used have to be registered under this parameter. But since its irrelevant dependency was identified, it was deprecated and direct usage of the directory object is recommended. Directory method provides direct and dynamic verification of the file path location.

Database Directories

As stated earlier, database directories are server side objects which point to a specific location on a system. The location can be either on the database server or on the client machine. By default, only SYSDBA enjoys the CREATE ANY DIRECTORY system privilege, so only a DBA can create the directory upon request. The user, who is using the directory, must have read/write access on it. Check the Syntax below to create the directory

If the location is on the server operating system, directory has to be created with the actual path.

Syntax

Example Code

If the location is on the client machine, the path needs to be shared and directory has to be created with the relative path. Relative path includes the client machine name and the shared path.

Syntax

Example Code

Note that the path verification process is not done during the directory creation. It is only during its use in UTL_FILE operation, when the associated path is verified and acted accordingly.