UTL_FILE Examples

SET serveroutput on size 1000000
DECLARE
  l_fh_read      UTL_FILE.file_type;
  l_fh_write     UTL_FILE.file_type;
  l_fh           UTL_FILE.file_type;
  l_path         VARCHAR2(200);
  l_fname_read   VARCHAR2(80);
  l_fname_write  VARCHAR2(80);
  l_buffer       VARCHAR2(32767);
BEGIN
  l_path        := '/home/users/smisra';  -- This directory must exist
  l_fname_read  := 'a.sql';               -- This file must exist and have read permissions
  l_fname_write := 'a.sql.copy'; -- This file should be writable  
  l_fh_read     := UTL_FILE.fopen(l_path, l_fname_read , 'r');
  l_fh_write    := UTL_FILE.fopen(l_path, l_fname_write, 'w');

  LOOP
     BEGIN
        UTL_FILE.get_line(l_fh_read,l_buffer);
     EXCEPTION
       WHEN NO_DATA_FOUND THEN
         l_buffer := NULL;
         EXIT;
     END ;
     UTL_FILE.put_line(l_fh_write,l_buffer);
  END LOOP;
  --
  --
  UTL_FILE.fclose(l_fh_read);
  UTL_FILE.fclose(l_fh_write);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_fh_read)
    THEN
      UTL_FILE.fclose(l_fh_read);
    END IF;
    IF UTL_FILE.is_open(l_fh_write)
    THEN
      UTL_FILE.fclose(l_fh_write);
    END IF;
    DBMS_OUTPUT.put_line('Error Code   :' || sqlcode);
    DBMS_OUTPUT.put_line('Error Message:' || sqlerrm);
END;
/