Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to output query results to CSV file using spool command

mariitaOct 17 2014 — edited Oct 22 2014

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

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;

Comments

unknown-7404

2776821 wrote:

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

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;

Correct - you have NOT terminated the sql statement.

Add a semicolon at the end of the last line of the query; that is, the line above that begins 'order by . .  .'

mariita

Thanks. It worked!

unknown-7404

Then please mark the question ANSWERED.


mariita

I don't seem to have any actions available to me -- at least, none that I can find. I'm not sure if I waited too long, or if there is a permissions issue. Apologies but it's my first time posting to this forum.

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 19 2014
Added on Oct 17 2014
4 comments
3,352 views