I am trying to export the data into EXCEL through PLSQL with the group by department.
Here is the code displaying Tabular output in Excel
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;
CURSOR
c_data1
is
SELECT DEPTNO FROM DEPT order by deptno;
v_file UTL_FILE.FILE_TYPE;
v_deptno varchar2(100);
V_COLUMNS VARCHAR2(3999);
BEGIN
SELECT 'EMPNO' || ',' ||
'ENAME' || ',' ||
'JOB ' || ',' ||
'MGR' || ',' ||
'HIREDATE' || ',' ||
'EMPNO' || ',' ||
'SAL ' || ',' ||
'COMM' || ',' ||
'DEPTNO'
INTO V_COLUMNS
FROM DUAL;
v_file := UTL_FILE.FOPEN('TEMP_DIR', 'shagar_test.csv', 'W');
UTL_FILE.PUT_LINE(v_file,V_COLUMNS);
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;
END LOOP;
UTL_FILE.FCLOSE(v_file);
exception
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
RAISE;
END;
Expected Output below;
