UTL File Processing

In ORACLE PL/SQL programs to write or read any kind of data from the file to the system and vice versa we will make use of the oracle standard API UTL FILE . 

To use this feature first user have to create both logical and physical directories in the data base server. If we are writing into the file then the user should have the write privilege and to read user should have the read privilege to the directory. This UTL_FILE package or API is owned by SYS user.

1. Create the Directory As Below.

CREATE OR REPLACE DIRECTORY XXDIRECTORY AS 'C:\abc\axefolder\GL\UTL_EX';

In the above XXDIRECTORY is a Directory name and  'C:\abc\axefolder\GL\UTL_EX' is a directory path in the oracle database server.

2. Grant the Privileges.

GRANT READ ON DIRECTORY XXDIRECTORY TO DBA;

3.Create the physical Directories.

4. Add the XXDIRECTORY path (i.e'C:\abc\axefolder\GL\UTL_EX') to UTIL_FILE parameter.

    We can check the current parameter value by using the below query 

SELECT value FROM v$parameter WHERE NAME='utl_file_dir';

5.Now we can able to use the newly created directory XXDIRECTORY for creating a new file in our 
    program.

Example:

DECLARE

L_file                UTL_FILE.file_type;

BEGIN

L_file      :=    UTL_FILE.FOPEN( location         =>'XXDIRECTORY',

                                                              filename        =>'Sample_File.txt',

                                                             open_mode   =>'W',

                                                              Max_linesize  =>32767);

 UTL_FILE.PUT_LINE(file =>L_file, buffer =>'XXXXX sample text XXXXXXXX');

 UTL_FILE.FCLOSE( file =>L_file);

END;

In UTL_FILE  package FILE_TYPE is a Record type in the UTL_FILE package.

UTL_FILE Subprograms:

Program Name	Signature	Description
FCLOSE  (P)	UTIL_FILE.FCLOSE(fine IN OUT FILE_TYPE)	Closes the file that we pass as a arguments
FCLOSE_ALL (P)	UTIL_FILE.FCLOSE_ALL	Closes all the files. This can be used in emergency  like when exception Raised use this in exception block to close all  files.
FCOPY(P)	UTL_FILE.FCOPY (    src_location    IN VARCHAR2,    src_filename    IN VARCHAR2,    dest_location   IN VARCHAR2,    dest_filename   IN VARCHAR2,    start_line      IN BINARY_INTEGER DEFAULT 1,    end_line        IN BINARY_INTEGER DEFAULT NULL);	It will copy the data from source file to the destination file from given Starting line to end line. If both start and end lines are omitted it will Copy entire file.
FFLUSH(P)	UTL_FILE.FFLUSH (   file  IN FILE_TYPE);	Normally data written to the file will be buffered and then physically Writes the data to the file. FFLUSH forces to write buffered data to file.
FGETATTR(P)	UTL_FILE.FGETATTR(    location     IN VARCHAR2,    filename     IN VARCHAR2,    fexists      OUT BOOLEAN,    file_length  OUT NUMBER,    block_size   OUT BINARY_INTEGER);	This procedure reads and returns the attributes of a disk file.
FGETPOS(F)	UTL_FILE.FGETPOS (    file IN FILE_TYPE)  RETURN PLS_INTEGER;	FGETPOS returns the relative offset position for an open file, in bytes.  It raises an exception if the file is not open. It returns 0 for the beginning of the file.
FOPEN(F)	UTL_FILE.FOPEN (    location     IN VARCHAR2,    filename     IN VARCHAR2,    open_mode    IN VARCHAR2,    max_linesize IN BINARY_INTEGER DEFAULT 1024)   RETURN FILE_TYPE;	This function opens the file in the specified mode with given max linesize In the given directory location and returns the FILE_TYPE. Open mode -> r -- read text, w -- write text, a -- append text                           rb -- read byte mode, wb -- write byte mode                          ab -- append byte mode Max_linesize  -> Range is from 1 to 32767
FOPENNCHAR(F)	UTL_FILE.FOPEN_NCHAR (    location     IN VARCHAR2,    filename     IN VARCHAR2,    open_mode    IN VARCHAR2,    max_linesize IN BINARY_INTEGER DEFAULT 1024) RETURN FILE_TYPE;	This opens the file in a National character set mode for input or output.
FREMOVE(P)	UTL_FILE.FREMOVE (    location IN VARCHAR2,    filename IN VARCHAR2);	If the user have the sufficient privileges then it delete's the disk file.
FRENAME(P)	UTL_FILE.FRENAME (    src_location     IN   VARCHAR2,    src_filename     IN   VARCHAR2,    dest_location    IN   VARCHAR2,    dest_filename    IN   VARCHAR2,    overwrite        IN   BOOLEAN DEFAULT FALSE);	This procedure renames an existing file to a new name.
FSEEK(P)	UTL_FILE.FSEEK (    file             IN OUT  UTL_FILE.FILE_TYPE,    absolute_offset  IN      PL_INTEGER DEFAULT NULL,    relative_offset  IN      PLS_INTEGER DEFAULT NULL);	This procedure adjusts the file pointer forward or backward within the file by the number of bytes specified.
GET_LINE(P)	UTL_FILE.GET_LINE (    file        IN  FILE_TYPE,    buffer      OUT VARCHAR2,    len         IN  PLS_INTEGER DEFAULT NULL);	This procedure reads text from the open file identified by the file handle and places the text in the output buffer parameter. Len- The number of bytes read from the file. Default is NULL. If NULL, Oracle supplies the value of max_linesize.
GEL_LINE_NCHAR(P)	UTL_FILE.GET_LINE_NCHAR (    file        IN  FILE_TYPE,    buffer      OUT NVARCHAR2,    len         IN  PLS_INTEGER DEFAULT NULL);	It is Similar to GET_LINE but reads In National character set mode and out's buffer in NVARCHAR2 type.
GET_RAW(P)	UTL_FILE.GET_RAW (    file       IN            UTL_FILE.FILE_TYPE,    buffer     OUT NOCOPY    RAW,    len        IN            PLS_INTEGER DEFAULT NULL);	This procedure reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read. UTL_FILE.GET_RAW ignores line terminators. Note: It raises NO_DATA_FOUND Exception when it attempts to read past the end of the file.
IS_OPEN(F)	UTL_FILE.IS_OPEN (    file  IN FILE_TYPE)   RETURN BOOLEAN;	It will returns TRUE if the file has been opened but not closed otherwise it returns FALSE
NEW_LINE(P)	UTL_FILE.NEW_LINE (    file     IN FILE_TYPE,    lines    IN BINARY_INTEGER := 1);	It writes one more line terminators to the file.
PUT(P)	UTL_FILE.PUT (    file      IN FILE_TYPE,    buffer    IN VARCHAR2);	It writes the text in the buffer parameter to the file that is opened. No line terminator is appended .
PUT_LINE(P)	UTL_FILE.PUT_LINE (    file      IN FILE_TYPE,    buffer    IN VARCHAR2,    autoflush IN BOOLEAN DEFAULT FALSE);	It writes the buffer data to the file appended with line terminator.
PUT_LINE_NCHAR(P)	UTL_FILE.PUT_LINE_NCHAR (    file    IN FILE_TYPE,    buffer  IN NVARCHAR2);	It is similar to PUT_LINE but it is used for national character set.
PUT_NCHAR(P)	UTL_FILE.PUT_NCHAR (    file      IN FILE_TYPE,    buffer    IN NVARCHAR2);	It is similar to PUT but it is specific to write national character set data to the file.
PUT_RAW	UTL_FILE.PUT_RAW (    file          IN    UTL_FILE.FILE_TYPE,    buffer        IN    RAW,    autoflush     IN    BOOLEAN DEFAULT FALSE);	This procedure 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 no nchar 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.