当设置sqlformat csv时，在SQLcl中设置heading无效

Question

我正在使用SQLcl生成csv输出。

set sqlformat csv  
set heading off  
select * from hr.employees where rownum < 10;  

"EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID"  
100,"Steven","King","SKING","515.123.4567",17-JUN-03,"AD_PRES",24000,,,90  
101,"Neena","Kochhar","NKOCHHAR","515.123.4568",21-SEP-05,"AD_VP",17000,,100,90  
102,"Lex","De Haan","LDEHAAN","515.123.4569",13-JAN-01,"AD_VP",17000,,100,90  
103,"Alexander","Hunold","AHUNOLD","590.423.4567",03-JAN-06,"IT_PROG",9000,,102,60  
104,"Bruce","Ernst","BERNST","590.423.4568",21-MAY-07,"IT_PROG",6000,,103,60  

但是我得到了我不想要的标题。我想set heading off应该关闭标题(就像在SQLPlus中一样)，为什么它在SQLcl中不起作用。如果我清除了sql格式化(set sqlformat)，那么关闭就可以了。这是SQLcl中的一个bug吗？

Answer 1

SET PAGESIZE 0

让你不用分页就能运行。

set heading off

应该还能用，但是坏了。下一版本将使用fixed...like命令

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

BARRY@orcl☘ >set sqlformat csv
BARRY@orcl☘ >set heading off
BARRY@orcl☘ >select * from demo;

1,"Barry"
2,"Lisa"
3,"Rebecca"
4,"Katie-Ellen"
BARRY@orcl☘ >

Answer 2

使用当前版本的sqlcl (截至2017年6月30日)，同时使用11g和12c数据库，可以很好地工作：

alter session set NLS_DATE_FORMAT='DD-MON-YYYY';
set feedback off
set sqlformat csv
spool <spool loc>
SELECT <column list> FROM <table list> WHERE <where clause
ORDER BY <order by clause>;
spool off;

Answer 3

你不想看到结果中的标题吗？

在SQLPLUS中，您可以使用

set pagesize 0