I have created a batch file to export Oracle SQL data to CSV file, when running only the query in SQL developer it's showing as expected but when I am executing the sql script with command prompt file data is accurate but the headers are not fully showing as expected only few characters are displaying.
Below are my SQL to extract the data to CSV file,
SET VERIFY OFF SERVEROUTPUT ON WRAP OFF
COLUMN TXT FORMAT A121 WORD_WRAPPED
SET GENERATE_HEADER = 'NO'
COL OBJECT_TYPE FORMAT A10000
COL OBJECT_NAME FORMAT A10000
SET TAB OFF
Set Newpage none
SET TRIMOUT ON
SET TRIMSPOOL ON
SET FEEDBACK OFF;
SET LINESIZE 32767
SET NUMWIDTH 120
SET COLSEP ,
SET PAGESIZE 50000 EMBEDDED ON
spool D:\salesexport\export.csv
SELECT * FROM NUM_EMPLOYEES;
EXIT
spool off
First of all, I get some errors here
SQL> SET VERIFY OFF SERVEROUTPUT ON WRAP OFF
Usage: SET SERVEROUTPUT { ON | OFF } [SIZE {n | UNL[IMITED]}]
[ FOR[MAT] { WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED] } ]
SP2-0158: unknown SET option "OFF"
SQL> COLUMN TXT FORMAT A121 WORD_WRAPPED
SQL> SET GENERATE_HEADER = 'NO'
SP2-0735: unknown SET option beginning "GENERATE_H..."
SQL> COL OBJECT_TYPE FORMAT A10000
SQL> COL OBJECT_NAME FORMAT A10000
SQL> SET TAB OFF
SQL> Set Newpage none
SQL> SET TRIMOUT ON
SQL> SET TRIMSPOOL ON
SQL> SET FEEDBACK OFF;
SQL> SET LINESIZE 32767
SQL> SET NUMWIDTH 120
SQL> SET COLSEP ,
SQL> SET PAGESIZE 50000 EMBEDDED ON
SQL> spool D:\export.csv
SQL> SELECT * FROM scott.emp;but if you want CSV ... just use the CSV markup option
SQL> set markup csv on
SQL> select * from scott.emp;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,"17-DEC-80",800,,20
7499,"ALLEN","SALESMAN",7698,"20-FEB-81",1600,300,30
7521,"WARD","SALESMAN",7698,"22-FEB-81",1250,500,30
7566,"JONES","MANAGER",7839,"02-APR-81",2975,,20
7654,"MARTIN","SALESMAN",7698,"28-SEP-81",1250,1400,30
7698,"BLAKE","MANAGER",7839,"01-MAY-81",2850,,30
7782,"CLARK","MANAGER",7839,"09-JUN-81",2450,,10
7788,"SCOTT","ANALYST",7566,"09-DEC-82",3000,,20
7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,,30
7876,"ADAMS","CLERK",7788,"12-JAN-83",1100,,20
7900,"JAMES","CLERK",7698,"03-DEC-81",950,,30
7902,"FORD","ANALYST",7566,"03-DEC-81",3000,,20
7934,"MILLER","CLERK",7782,"23-JAN-82",1300,,10