Oracle: writing to a file

Oracle has a package that allows you to write to a file. Oracle uses the concept of a directory (here “External”) that acts as a logical description of a directory. One of the properties of such directory is the physical location.

That file can be opened, written to and closed. Subsequent programme provides an example.

create or replace PROCEDURE writeEmployee as
  TextRecord   VARCHAR2(2000);
  TextFile     utl_file.FILE_TYPE;
  CURSOR EmployeeList IS
     SELECT EName, Sal, Comm
     FROM emp e
     ORDER BY EName;
  TYPE EmployeeRecordType IS RECORD
     (EmpName   emp.EName%TYPE,
      EmpSalary emp.Sal%TYPE,
      Commission  emp.Comm%TYPE);
  EmployeeRecord   EmployeeRecordType;
BEGIN 
  OPEN EmployeeList;
  TextFile := utl_file.fopen ('EXTERNAL', 'Employees.csv', 'w', 32767);
  LOOP
    FETCH EmployeeList INTO EmployeeRecord;
    EXIT WHEN EmployeeList%NOTFOUND;
    TextRecord := EmployeeRecord.EmpName || ',' ||
                  EmployeeRecord.EmpSalary  || ',' ||
                  EmployeeRecord.Commission;
    utl_file.put_line (TextFile, TextRecord);
  END LOOP;
  CLOSE EmployeeList;
  utl_file.fclose (TextFile);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line (sqlcode);
    dbms_output.put_line (sqlerrm);
END; 
/