1

I am generating a csv output using SQLcl. 

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

But I am getting the heading which I don't want. I imagine set heading off should turn off the heading (as it does in SQLPlus) why it's not working in SQLcl. If I clear the sql formatting (set sqlformat) then heading off works. Is it a bug in SQLcl?

Sabyasachi Mitra
  • 365
  • 1
  • 4
  • 12

3 Answers3

2
SET PAGESIZE 0

lets you run without pagination.

set heading off

should work, but was broken. The next release will have it fixed...like this

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☘ >
Barry McGillin
  • 465
  • 5
  • 11
2

Using the current version of sqlcl (as of 6/30/2017), with both 11g and 12c databases, works fine:

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;
mongodbOracle
  • 21
  • 2
-1

You don't want to see the headings in the results?

In SQLPLUS you can use

set pagesize 0
connollyc4
  • 155
  • 1
  • 3
  • 15