How to read/write file in oracle with pl/sql

The UTL_FILE package can be used to read or write file from operating system. The UTL_FILE package has different subprograms which will help to read and write file from/to OS.

Steps for writing to a OS file system through PL/SQL
Note that this example is under windows file system.
Step 01: Create directory.

SQL>Create or replace directory "FILE_DIR" as 'G:\Logs';

Note that FILE_DIR name is the alias of the physically existing directory of G:\Logs. So there must exists Logs directory under G directory. And to create directory the user must have DBA role or create directory privilege.

Step 02: Give permission to the user who will use the directory FILE_DIR.
If read permission is given then following user can read file from the directory. If write permission is given then following user can write to the directory. Here I have given read and write permission to user ARJU on directory FILE_DIR by two statements.

SQL> GRANT READ ON DIRECTORY FILE_DIR TO ARJU;
SQL> GRANT WRITE ON DIRECTORY FILE_DIR TO ARJU;

You can give both permission by only one statement as,

SQL> GRANT READ, WRITE ON DIRECTORY FILE_DIR TO ARJU;

Also you can give permission to all database users by,

SQL> GRANT READ, WRITE ON DIRECTORY FILE_DIR TO PUBLIC;

Step 03: Provide access to UTL_FILE package

SQL>GRANT EXECUTE ON UTL_FILE TO ARJU;

Step 04: As user ARJU write to the file.

SQL>CONN ARJU/a

SQL>DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
  BEGIN
  fileHandler := UTL_FILE.FOPEN('FILE_DIR', 'myfile.txt', 'w');
  UTL_FILE.PUTF(fileHandler, 'This is the first Line. \nThis is the second line.');
  UTL_FILE.FCLOSE(fileHandler);
END;
/

After executing above PL/SQL, under G:\Logs myfile.txt would be created and contents of it would be,

This is the first Line.
This is the second line.


Steps for reading file from OS file system through PL/SQL.
After step 1,2 and 3 of above execute following PL/SQL.

SQL>set serverout on
SQL>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;
/

Here for printing two lines I call UTL_FILE.GET_LINE two times as UTL_FILE.GET_LINE stops reading whenever it finds a newline or by default up to 1024 bytes.