UTL_FILE

UTL_FILE:

             UTL_FILE is a package that has been welcomed warmly by PL/SQL developers. It allows PL/SQL programs to both read from and write to any operating system files that are accessible from the server on which your database instance is running.

UTL_FILE Programs:

Subprogram	Description
FCLOSE Procedure	Closes a file
FCLOSE_ALL Procedure	Closes all open file handles
FCOPY Procedure	Copies a contiguous portion of a file to a newly created file
FFLUSH Procedure	Physically writes all pending output to a file
FGETATTR Procedure	Reads and returns the attributes of a disk file
FGETPOS Function	Returns the current relative offset position within a file, in bytes
FOPEN Function	Opens a file for input or output
FOPEN_NCHAR Function	Opens a file in Unicode for input or output
FREMOVE Procedure	Deletes a disk file, assuming that you have sufficient privileges
FRENAME Procedure	Renames an existing file to a new name, similar to the UNIX mv function
FSEEK Procedure	Adjusts the file pointer forward or backward within the file by the number of bytes specified
GET_LINE Procedure	Reads text from an open file
GET_LINE_NCHAR Procedure	Reads text in Unicode from an open file
GET_RAW Function	Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
IS_OPEN Function	Determines if a file handle refers to an open file
NEW_LINE Procedure	Writes one or more operating system-specific line terminators to a file
PUT Procedure	Writes a string to a file
PUT_LINE Procedure	Writes a line to a file, and so appends an operating system-specific line terminator
PUT_LINE_NCHAR Procedure	Writes a Unicode line to a file
PUT_NCHAR Procedure	Writes a Unicode string to a file
PUTF Procedure	A PUT procedure with formatting
PUTF_NCHAR Procedure	A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
PUT_RAW Function	Accepts as input a RAW data value and writes the value to the output buffer

UTL_FILE Exceptions:

Exception Name	Description
INVALID_PATH	File location is invalid.
INVALID_MODE	The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
CHARSETMISMATCH	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN	The requested operation failed because the file is open.
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.
ACCESS_DENIED	Permission to access to the file location is denied.
INVALID_OFFSET	Causes of the INVALID_OFFSET exception: ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or ABSOLUTE_OFFSET < 0, or Either offset caused a seek past the end of the file
DELETE_FAILED	The requested file delete operation failed.
RENAME_FAILED	The requested file rename operation failed.

Create Directory & Grant:

SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log';

SQL> GRANT READ ON DIRECTORY log_dir TO DBA;

SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user'';

SQL> GRANT READ ON DIRECTORY user_dir TO PUBLIC;