How to output query results to CSV file using spool command in Oracle SQL Developer

Question

I am trying to output the results of a SQL query to a CSV file using the SPOOL command in Oracle SQL Developer.

I am able to output the results of a trivial query by calling it as a script. Here is the query that worked:

spool trivial_output.csv
select /*csv*/ * from trivial_table;
spool off;

And this is how I successfully called it (F5):

@'C:\Spool to CSV\trivial_query.sql'

However, when I attempt the exact same thing with a slightly more complex query, I get the error message: "SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - SQL command not properly ended"

spool total_records.csv
select  /*csv*/     enrol.year, enrol.college, count(*) as "Total Records"
from        enrolment enrol
inner join  regis_status_type regstatus
on          enrol.regis_status_type_id = regstatus.regis_status_type_id
where       enrol.year in ('201213', '201314')
and         regstatus.regis_status_type_code in ('10','41')
group by    enrol.year, enrol.college
order by    enrol.year, enrol.college
spool off;

You probably need some hard coded commas in your select clause. In fact, may as well do it properly and have some hard coded double quotes as well in case any of the colleges contain a comma. — Dan Bracuk
– Dan Bracuk, Commented Oct 17, 2014 at 18:39
I'm not sure if it matters, but the Oracle error message says the error is triggered just before the final line ("spool off"). So the problem seems to occur later than the SELECT statement. — b00kgrrl
– b00kgrrl, Commented Oct 17, 2014 at 18:43
It appears that you are missing the semicolon after your SELECT statement in the second example. That causes Oracle to interpret the spool off as the last line of your SQL statement rather than as a separate SQL*Plus command. That, in turn, causes your SQL statement to be syntactically invalid. — Justin Cave
– Justin Cave, Commented Oct 17, 2014 at 20:35
Thanks, @justin-cave. Are you saying that there should be a semicolon after the ORDER BY clause? I will try your suggestion on Monday when I am at work. — b00kgrrl
– b00kgrrl, Commented Oct 18, 2014 at 19:45
You're right... I needed to add a semi-colon after the ORDER BY clause. — b00kgrrl
– b00kgrrl, Commented Oct 20, 2014 at 12:57

Community · Accepted Answer · 2017-05-23 12:24:43Z

3

I just needed to add a semi-colon to separate the SQL*Plus command from the SQL statement. Thanks to Justin Cave

spool total_records.csv
select  /*csv*/     enrol.year, enrol.college, count(*) as "Total Records"
from        enrolment enrol
inner join  regis_status_type regstatus
on          enrol.regis_status_type_id = regstatus.regis_status_type_id
where       enrol.year in ('201213', '201314')
and         regstatus.regis_status_type_code in ('10','41')
group by    enrol.year, enrol.college
order by    enrol.year, enrol.college;
spool off;

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Oct 20, 2014 at 12:59

b00kgrrl

5973 gold badges10 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon Hughes · Accepted Answer · 2016-08-05 09:27:55Z

-1

spool "D:\test\test.txt"  

select  
    a.ename  
from  
    employee a  
inner join department b  
on  
(  
    a.dept_id = b.dept_id  
)  
;  
spool off

This query will spool the sql result here D:\test\test.txt

edited Aug 5, 2016 at 9:27

Simon Hughes

3,5943 gold badges27 silver badges46 bronze badges

answered Aug 5, 2016 at 8:39

Sindhu

4161 gold badge4 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to output query results to CSV file using spool command in Oracle SQL Developer

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related