Reading File, Writing File through UTL_FILE - PL/SQL

Background:
With the UTL_FILE package, your PL/SQL programs can read and write operating system text files.

Prerequisite steps:
1. Create UTF_FILE directory.
    CREATE OR REPLACE DIRECTORY MY_UTL_DIR AS '/usr/tmp/utl'
2. Now keep a simple text file 'temp.txt' in '/usr/tmp/utl' directory. My temp.txt file has 10 lines.


Program 1:
Now lets try a simple program which reads the file temp.txt and print on the console.

DECLARE

   l_fileID           UTL_FILE.FILE_TYPE;

   l_dirpath         VARCHAR2 (50) := 'MY_UTL_DIR';

   l_filename       VARCHAR2 (50) := 'temp.txt';

   l_line_counter NUMBER := 1;

   l_buffer           VARCHAR2(32000);

BEGIN

   /*Open file*/

   l_fileID := UTL_FILE.FOPEN (l_dirpath, l_filename, 'r', 32000);

      LOOP

         /*Read and output the line until we reach the last line*/

         UTL_FILE.GET_LINE(l_fileID, l_buffer, 32000);

         dbms_output.put_line('Line' || l_line_counter: ||'   '|| l_buffer);

          l_line_counter := l_line_counter + 1;

      END LOOP;

EXCEPTION

   WHEN UTL_FILE.INVALID_PATH THEN

      DBMS_OUTPUT.PUT_LINE('File location is invalid');

   WHEN NO_DATA_FOUND THEN /*EOF Reached*/

      UTL_FILE.fclose(l_fileID); /* Close the File Type*/

      NULL;

END;

Program 2:
Now lets try a simple write program to write 10 lines to the file TestWrite.txt

DECLARE

   l_fileID           UTL_FILE.FILE_TYPE;

   l_dirpath         VARCHAR2 (50) := 'MY_UTL_DIR';

   l_filename       VARCHAR2 (50) := 'TestWrite.txt';

   l_buffer           VARCHAR2(32000);

   l_count            NUMBER :=0;

BEGIN

   /*Open file*/

   l_fileID := UTL_FILE.FOPEN (l_dirpath, l_filename, 'w', 32000);

      LOOP

         l_count := l_count+1;

          /*PUT_LINE procedure writes the text string stored in the buffer to the open file*/

         UTL_FILE.PUT_LINE(l_fileID, 'This is Line Number: ' || l_count);

         EXIT WHEN l_count = 11;

      END LOOP;

   UTL_FILE.fclose(l_fileID); /* Close the File Type*/

EXCEPTION

   WHEN UTL_FILE.WRITE_ERROR THEN

      DBMS_OUTPUT.PUT_LINE('Operating system error occurred during the write operation.');

   WHEN others THEN

      DBMS_OUTPUT.PUT_LINE('Other Exception.');

END;

Note:
UTL_FILE documentation:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm