使用批处理文件将Oracle SQL导出为CSV时，标头未完全导出

Question

我创建了一个批处理文件，用于将Oracle SQL数据导出到CSV文件，当在SQL开发人员中仅运行查询时，它会按预期显示，但当我使用命令提示符执行SQL脚本时，文件数据是准确的，但标题没有按预期完全显示，只显示了几个字符。

下面是我的SQL将数据提取到CSV文件，

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:salesexportexport.csv 
SELECT * FROM NUM_EMPLOYEES;
EXIT
spool off

Answer 1

SQL Developer有用于生成CSV输出的内置工具。

单向：

select /*csv*/ * from scott.emp;

(csv周围没有空格(，然后使用"运行为脚本"按钮。或者，正常运行，右键单击结果网格并选择"导出"。

但是，您的问题似乎出现在SQL*Plus中。

如果您有SQL*Plus 12.2或更高版本(请注意，这是Oracle客户端版本，而不是数据库版本(，则可以使用set markup csv:

set markup csv on

例如：

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Aug 20 10:36:05 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Sat Aug 20 2022 10:35:40 +01:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set markup csv on
SQL>
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,"19-APR-87",3000,,20
7839,"KING","PRESIDENT",,"17-NOV-81",5000,,10
7844,"TURNER","SALESMAN",7698,"08-SEP-81",1500,0,30
7876,"ADAMS","CLERK",7788,"23-MAY-87",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
14 rows selected.
SQL>

对于早期版本的SQL*Plus，您必须手动编写查询代码，包括所有格式(to_number、to_date等(和连接。