UTL_FILE with example

Introduction:

UTL_FILE is used to read and write operating System Flat Files.UTL_FILE runs on the server side, not from the Client side.

Permissions:

 All valid directories must be explicitly mentioned in the init.ora file(ask DBA TO set init.ora )
To give all users read/write permissions to the directory, issue the following command from the UNIX prompt:

   chmod 777 <directory name>'

Example Test Code:

The following is a simple example demonstrating how to write procedure that writes file to an operating system flat file. This is Tested Code. You can use Your Reference.Just Change Cursor according to Your Requirement.
THIS PROCEDURE WRITES A FILE WITH NAME TEST ON SERVER .
Example to Create Proc for UTL_FILE:
/**** START OF PROCEDURE ***********/

CREATE OR REPLACE Procedure TEST_UTL AS

l_fp                   UTL_FILE.FILE_TYPE;---- file handle of OS flat file

l_mode                 VARCHAR2(1)   := 'W'; --mode of File

l_buffer               VARCHAR2(5000) ;

l_location             VARCHAR2(300) :='/u08/applmast/mastcomn/temp' ; /*Location of file On Server */

l_file                VARCHAR2(100) :=  'TEST';--File Name

CURSOR C_VENDOR IS

SELECT

POV.VENDOR_NAME,

POV.SEGMENT1,

POVS.ADDRESS_LINE1,

POVS.CITY,

POVS.STATE

FROM

PO_VENDORS POV,

PO_VENDOR_SITES_ALL POVS

WHERE

POV.VENDOR_ID = POVS.VENDOR_ID

and POV.vendor_id = 46;  --Hardcoed For Example.

BEGIN

BEGIN

/***open file to write into and obtain its file_handle(for example I am using variable l_fp as a File Handler in this Example.) ***/

l_fp := UTL_FILE.FOPEN(l_location,l_file,l_mode);

FOR V_VENDOR IN C_VENDOR LOOP

l_buffer  := V_VENDOR.VENDOR_NAME||','||V_VENDOR.SEGMENT1||','||V_VENDOR.ADDRESS_LINE1||','||

V_VENDOR.CITY||','||V_VENDOR.STATE;

/*** Write a line of text out to the file using UTL_FILE.PUT_LINE *****/

UTL_FILE.Put_line(l_fp,l_buffer);

END LOOP;

UTL_FILE.FCLOSE(l_fp);

END;

/*** EXCEPTIONS OF UTL_FILE ****/

EXCEPTION

   when utl_file.invalid_path then

      dbms_output.put_line('>>> Invalid path');

   when utl_file.invalid_filehandle then

      dbms_output.put_line('>>> Invalid file handle');

   when utl_file.invalid_mode then

      dbms_output.put_line('>>> Invalid mode');

   when utl_file.invalid_operation then

      dbms_output.put_line('>>> Invalid operation');

   when utl_file.write_error then

      dbms_output.put_line('>>> Write error');

   when utl_file.read_error then

      dbms_output.put_line('>>> Read error');

   when utl_file.internal_error then

      dbms_output.put_line('>>> Internal error');

WHEN OTHERS THEN

 DBMS_OUTPUT.PUT_LINE('other stuff'||sqlerrm);

/*** CLOSE OF FILE *****/

END;

/

****END OF PROCEDURE *****/

SIZE OF BUFFER:

The maximum size for the buffer in any PUT_LINE procedure is 32767 Characters.
   The maximum size for the buffer in any GET_LINE procedure is 32767  Characters.

EXCEPTIONS OF UTL_FILE

1. UTL_FILE.INVALID_PATH: This Exception Occurs When Path Specified is Invalid.
2. UTL_FILE.INVALID_FILEHANDLE: This Exception Occurs because of Invalid File Handler for example I used file handler in my Procedure name l_fp.
3. UTL_FILE.INVALID_MODE: Mode of File is Invalid. UTL have only three mode Read(R), Write(W),Append(A)
4. UTL_FILE.INVALID_OPERATION: You received an unhandled UTL_FILE.INVALID_OPERATION exception. The file cannot be created because of permissions in given directory or when you     are trying to write to someone else's file without having Proper permissions.
5. UTL_FILE.WRITE_ERROR: Write error.
6. UTL_FILE.READ_ERROR:  Read Error.
7. UTL_FILE.INTERNAL_ERROR: Internal Errr.

COMMANDS OF UTL_FILE

UTL_FILE.FOPEN(For opening of File)

UTL_FILE.Put_line(Write a line of text)

UTL_FILE.FCLOSE(Closing the File)

UT_FILE.GET_LINE(For Reading File)

MODE OF UTL_FILE

1. Read(‘R’)—For Reading the File From server.
2. Write(‘W’)—For Writing the File.
3. Append(‘A’)—For adding records in Exciting File.

File Handler

File Handler to handle the File. For example in my cord I used File Handler UTL_FILE.FILE_TYPE.

When you open the File File Handler demands Three Parameter.
(<Location>,<File_name>,<Mode_of_file>).
For example I used in my Procedure File handler with Variable name l_fp(u can use any name) when I opened the File.
l_fp := UTL_FILE.FOPEN(l_location,l_file,l_mode);(see Proc for Ref).