UTL FILE

UTL_FILE is a PL/SQL package that can be used to write to operating system files on a local file system where the database resides.

Example

Create a database directory:

CREATE OR REPLACE DIRECTORY tmp AS '/tmp';

Write a file out to disk:

DECLARE
  fHandle UTL_FILE.FILE_TYPE;
BEGIN
  fHandle := UTL_FILE.FOPEN('TMP', 'myoutput', 'W');
  UTL_FILE.PUTF(fHandle, '   --- Heading 1 ---\n');
  UTL_FILE.PUTF(fHandle, '      --- Heading 2 ---\n');
  UTL_FILE.PUTF(fHandle, '         --- Heading 3 ---\n');
  UTL_FILE.FCLOSE(fHandle);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid path for file.');
END;
/

Read the file back in:

DECLARE
  fhandle UTL_FILE.FILE_TYPE;
  line    VARCHAR2(80);
BEGIN
  fhandle := UTL_FILE.FOPEN('TMP', 'myoutput', 'R');
  LOOP
    UTL_FILE.GET_LINE(fhandle, line);
    IF line IS NULL THEN
       dbms_output.put_line('Got empty line');
    ELSE
       dbms_output.put_line('Non empty line: '||line);
    END IF;
  END LOOP;
EXCEPTION
  WHEN no_data_found THEN
     dbms_output.put_line('No more data to read');
END;
/