CREATE TABLE emp
(
  EMPNO     NUMBER(4)                           NOT NULL,
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  address   varchar2(100),
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2)
);

Insert into emp
   (EMPNO, ENAME, JOB, MGR, ADDRESS, HIREDATE, SAL, DEPTNO)
 Values
   (7369, 'SMITH', 'CLERK', 7902, 'PEI,CANADA', TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 800, 20);
Insert into emp
   (EMPNO, ENAME, JOB, MGR, ADDRESS, HIREDATE, SAL, DEPTNO)
 Values
   (7499, 'ALLEN', 'SALESMAN', 7698, 'Bangalore,INDIA', TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1600, 30);
Insert into emp
   (EMPNO, ENAME, JOB, MGR, ADDRESS, HIREDATE, SAL, DEPTNO)
 Values
   (7521, 'WARD', 'SALESMAN', 7698, '80,hyderabad,india', TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1250, 30);
Insert into emp
   (EMPNO, ENAME, JOB, MGR, ADDRESS, HIREDATE, SAL, DEPTNO)
 Values
   (7566, 'JONES', 'MANAGER', 7839, '#20,PEI,canada', TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2975, 20);
Insert into emp
   (EMPNO, ENAME, JOB, MGR, ADDRESS, HIREDATE, SAL, DEPTNO)
 Values
   (7782, 'CLARK', 'MANAGER', 7839, '#10-20,US', TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2450, 10);

set echo off
set verify off
set termout on
set heading off
set pages 50000
set feedback off
set newpage none
set linesize 160
spool e:/file_csv.csv

SELECT 'EMPNO,ENAME,JOB,ADDRESS,MGR,HIREDATE,DEPTNO' from dual
union all
SELECT empno||','||ename||','||job||','||address||','||mgr||','||hiredate||','||DEPTNO
FROM emp
WHERE deptno in (10,20);

spool off;
exit;

set echo off
set verify off
set termout on
set heading off
set pages 50000
set feedback off
set newpage none
set linesize 160
set colsep '|'

spool e:/file_csv.csv

SELECT 'EMPNO|ENAME|JOB|ADDRESS|MGR|HIREDATE|DEPTNO' from dual
union all
SELECT empno||'|'||ename||'|'||job||'|'||address||'|'||mgr||'|'||hiredate||'|'||DEPTNO
FROM emp
WHERE deptno in (10,20);

spool off;
exit;