Below is the step by step example is given to export data into CSV file in Oracle using PL SQL procedure. In this example, data is being exported from some fields of Emp table of Scott schema to a CSV file using UTL_FILE Oracle package.
Follow the below steps to export data from Oracle Database table to a CSV file.
1. Create a Directory Object in which you will write CSV file.
-- for windows systems create as following change the folder highlighted with your directory
CREATE OR REPLACE DIRECTORY CSVDIR AS 'd:\temp' /
-- for Unix/Linux systems create as following
CREATE OR REPLACE DIRECTORY CSVDIR AS '/temp/' /
Note: Change the folder location to your directory location, e.g., c:\abc or /abc/.
2. Create a Database Procedure in Scott schema or if you are creating in another schema then make sure you have the Emp table in that schema, else you need to change the Cursor and the field references for any other table.
CREATE OR REPLACE PROCEDURE export_to_csv IS   v_file   UTL_FILE.file_type;   v_string  VARCHAR2 (4000);   CURSOR c_emp   IS    SELECT empno,        ename,        deptno,        sal,        comm     FROM emp; BEGIN   v_file :=    UTL_FILE.fopen ('CSVDIR',            'empdata.csv',            'w',            1000);   -- if you do not want heading then remove below two lines   v_string := 'Emp Code, Emp Name, Dept, Salary, Commission';   UTL_FILE.put_line (v_file, v_string);   FOR cur IN c_emp   LOOP    v_string :=       cur.empno      || ','      || cur.ename      || ','      || cur.deptno      || ','      || cur.sal      || ','      || cur.comm;    UTL_FILE.put_line (v_file, v_string);   END LOOP;   UTL_FILE.fclose (v_file); EXCEPTION   WHEN OTHERS   THEN    IF UTL_FILE.is_open (v_file)    THEN      UTL_FILE.fclose (v_file);    END IF; END;
3. Now run the procedure as follows:
BEGIN Â Â export_to_csv; END;
You can now check your directory you specified in Create Directory command that the file empdata.csv must exist with the table data. I have created a utility which generates the procedure online try it: