SPOOL returns empty files when trying to export from SQL Developer

Question

I have a series of queries that I want to output to .csv files. The only tool I have to query the database is SQL Developer.

I could run each query and then use the Export dialogue in SQL Developer to write them to files, but that's cumbersome, particularly when this needs to be done for multiple files every day.

This works for some people Directly export a query to CSV using SQL Developer

But it doesn't work for me.

For example, if I try

spool "C:\Users\james.foreman\Downloads\Temp\myfile.csv"

select distinct placement_type
FROM jf_placements;

spool off;

then in the script output pane of SQL Developer, I see

Cannot create SPOOL file C:\Users\james.foreman\Downloads\Temp\myfile.csv

and although myfile.csv is created, there's no results. (There are two rows returned by the query.)

My first thought was that there was a permissions issue writing to C:\Users\james.foreman\Downloads\Temp but that doesn't appear to be the case, because if I delete the myfile.csv and then run the SQL, the myfile.csv file is recreated, but it never has anything in it.

So I assume this is a configuration issue, either with the Windows machine I'm running SQL Developer on, or with my SQL Developer set up. Where should I look to investigate further?

@Devolus 's answer to Procedure to export table to multiple csv files includes the instruction "In the SQL Window right click -> Change Window to -> Command Window" but if I right click on the SQL Window, I don't see a Change Window option.

(Running Windows 7, SQL Developer Version 4.0.2.15, Build 15.21, database is Oracle 11.2)

Answer 1

When you run your script, press "Run Script" instead of "Run Statment" or you may press F5.

It fixed my problem, hope it can fix yours too

Answer 2

The fact that the file is created, but has no data, perhaps, the last statement, SPOOL OFF is not yet executed. Add a new line in the script and try again.

For example, your script would look like :

    spool "C:\Users\james.foreman\Downloads\Temp\myfile.csv"

    select distinct placement_type
    FROM jf_placements
    /

    spool off
    /

-- need a new line to make sure spool off executes