Oracle read/write into file

Write to file
declare
  output utl_file.file_type;
  line   varchar2(20000);
begin
--  execute immediate 'alter session set NLS_CHARACTERSET = CL8MSWIN1251';
  output := utl_file.fopen(location => 'directory_name', filename => to_char(sysdate, 'file_name', open_mode => 'A');
  for rec in
    ()
  loop
    line := rec.field1|| '|' || rec.field2 || '|' || rec.field3 || '|' || rec.field4 || '|' || rec.field5;
    utl_file.put(output, line); 
    utl_file.new_line(file => output);
  end loop;
  utl_file.fclose(file => output);
end;

Read from file

example 1

DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
  buffer    CLOB;
BEGIN
  fileHandler := UTL_FILE.FOPEN('FILE_DIR', 'myfile.txt', 'r');
  UTL_FILE.GET_LINE(fileHandler, buffer);
  dbms_output.put_line('File Data: '||buffer);
  UTL_FILE.GET_LINE(fileHandler, buffer);
  dbms_output.put_line(buffer);
  UTL_FILE.FCLOSE(fileHandler);
END;

example 2

DECLARE

  vInHandle utl_file.file_type;

  vNewLine  VARCHAR2(250);

BEGIN

   vInHandle := utl_file.fopen('ORALOAD', 'test.txt', 'R');

   LOOP

     BEGIN

       utl_file.get_line(vInHandle, vNewLine);

       dbms_output.put_line(vNewLine);

     EXCEPTION

       WHEN OTHERS THEN

       EXIT;

     END;

   END LOOP;

   utl_file.fclose(vInHandle);

END;

example 3

create table test (fld1 VARCHAR2(20),fld2 VARCHAR2(20));



CREATE OR REPLACE PROCEDURE
read_demo(file_name VARCHAR2) IS

  vSFile  
utl_file.file_type;

  vNewLine VARCHAR2(200);

BEGIN

  vSFile := utl_file.fopen('ORALOAD', file_name, 'r');



  IF utl_file.is_open(vSFile) THEN

    LOOP

      BEGIN

        utl_file.get_line(vSFile, vNewLine);

      

        IF vNewLine IS NULL THEN

          EXIT;

        END IF;

      

        INSERT INTO test (fld1, fld2) VALUES (vNewLine,
file_name);

      EXCEPTION

        WHEN NO_DATA_FOUND THEN

          EXIT;

      END;

    END LOOP;

    COMMIT;

  END IF;

  utl_file.fclose(vSFile);

  utl_file.frename('ORALOAD', 'test.txt', 'ORALOAD', 'x.txt', TRUE);

EXCEPTION

  WHEN utl_file.invalid_mode THEN

    RAISE_APPLICATION_ERROR(-20051, 'Invalid Mode
Parameter');

  WHEN utl_file.invalid_path THEN

    RAISE_APPLICATION_ERROR(-20052, 'Invalid File
Location');

  WHEN utl_file.invalid_filehandle THEN

    RAISE_APPLICATION_ERROR(-20053, 'Invalid
Filehandle');

  WHEN utl_file.invalid_operation THEN

    RAISE_APPLICATION_ERROR(-20054, 'Invalid
Operation');

  WHEN utl_file.read_error THEN

    RAISE_APPLICATION_ERROR(-20055, 'Read Error');

  WHEN utl_file.internal_error THEN

    RAISE_APPLICATION_ERROR(-20057, 'Internal Error');

  WHEN utl_file.charsetmismatch THEN

    RAISE_APPLICATION_ERROR(-20058,

                            'Opened With
FOPEN_NCHAR

    But Later I/O Inconsistent');

  WHEN utl_file.file_open THEN

    RAISE_APPLICATION_ERROR(-20059, 'File Already
Opened');

  WHEN utl_file.invalid_maxlinesize THEN

    RAISE_APPLICATION_ERROR(-20060, 'Line Size
Exceeds 32K');

  WHEN utl_file.invalid_filename THEN

    RAISE_APPLICATION_ERROR(-20061, 'Invalid File
Name');

  WHEN utl_file.access_denied THEN

    RAISE_APPLICATION_ERROR(-20062, 'File Access
Denied By');

  WHEN utl_file.invalid_offset THEN

    RAISE_APPLICATION_ERROR(-20063, 'FSEEK Param Less
Than 0');

  WHEN others THEN

    RAISE_APPLICATION_ERROR(-20099, 'Unknown UTL_FILE
Error');

END read_demo;

/