OracleApps02: Create .csv File and placed on Server using PL/SQL

To create a file, we need to create a directory and have the read write permission as

1) create or replace directory XXDIR as '/home/oracle/mycsv'

Note: /home/oracle/xxdir has to be physical location on disk.

2) grant read, write on directory XXDIR to user

Following is the pl/sql sample code to create CSV file

DECLARE
    F UTL_FILE.FILE_TYPE;
    CURSOR C1 IS SELECT xxcolumn from xxtable;
BEGIN
    F := UTL_FILE.FOPEN('XXDIR','EMP_DEPT.CSV','w',32767);
    FOR i IN C1
    LOOP
        UTL_FILE.PUT(F,i.xxcolumn);
        UTL_FILE.NEW_LINE(F);
    END LOOP;
    UTL_FILE.FCLOSE(F);
END;
/