CSV Generation from PLSQL Procedure

Question

I tried to create the CSV file through PLSQL and It's made. I want to know how to include the Header for each column.

Code;

CREATE OR REPLACE PROCEDURE EMP_CSV_EX AS
 
  CURSOR c_data IS
    SELECT a.empno,
           a.ename,
           a.job,
           a.mgr,
           TO_CHAR(a.hiredate,'DD-MON-YYYY') AS hiredate,
           a.sal,
           a.comm,
           b.deptno
    FROM   emp a,dept b
    where a.deptno = b.deptno
    ORDER BY b.deptno;
    
  v_file  UTL_FILE.FILE_TYPE;
  
  v_deptno varchar2(100);
BEGIN
 v_file := UTL_FILE.FOPEN('TEMP_DIR', 'shagar_test.csv', 'W');
  FOR cur_rec IN c_data LOOP
    
    UTL_FILE.PUT_LINE(v_file,
                      cur_rec.empno    || ',' ||
                      cur_rec.ename    || ',' ||
                      cur_rec.job      || ',' ||
                      cur_rec.mgr      || ',' ||
                      cur_rec.hiredate || ',' ||
                      cur_rec.empno    || ',' ||
                      cur_rec.sal      || ',' ||
                      cur_rec.comm     || ',' ||
                      cur_rec.deptno);
  END LOOP;
  UTL_FILE.FCLOSE(v_file);
  
    exception
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE;
END;
/

Current Output
enter image description here

Expecting Output

enter image description here

Answer 1

Simply add a new line before you loop through the data

-- Write the header line
UTL_FILE.PUT_LINE(v_file, 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO');

So your entire procedure definition would be:

CREATE OR REPLACE PROCEDURE EMP_CSV_EX AS
  CURSOR c_data IS
    SELECT a.empno,
           a.ename,
           a.job,
           a.mgr,
           TO_CHAR(a.hiredate,'DD-MON-YYYY') AS hiredate,
           a.sal,
           a.comm,
           b.deptno
    FROM   emp a, dept b
    WHERE  a.deptno = b.deptno
    ORDER BY b.deptno;
    
  v_file  UTL_FILE.FILE_TYPE;

BEGIN
  v_file := UTL_FILE.FOPEN('TEMP_DIR', 'shagar_test.csv', 'W');
  
  -- Write the header line
  UTL_FILE.PUT_LINE(v_file, 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO');
  
  FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file,
                      cur_rec.empno    || ',' ||
                      cur_rec.ename    || ',' ||
                      cur_rec.job      || ',' ||
                      cur_rec.mgr      || ',' ||
                      cur_rec.hiredate || ',' ||
                      cur_rec.sal      || ',' ||
                      cur_rec.comm     || ',' ||
                      cur_rec.deptno);
  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;
    RAISE;
END;
/