CODEWITHSUNDEEP
sqloracle-sqldeveloperspool
b00kgrrl
b00kgrrl

Reputation: 597

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

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;

Upvotes: 4

Views: 27474

Answers (2)

Sindhu
Sindhu

Reputation: 418

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

Upvotes: -1

b00kgrrl
b00kgrrl

Reputation: 597

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;

Upvotes: 3

Related Questions