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 in the database is SQL Developer.

I can run each query and then use the Export dialog in SQL Developer to write them to files, but this is cumbersome, especially when you need to do this for multiple files every day.

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

But this does not 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 output area of ​​the script SQL Developer I see

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

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

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

So, I assume that this is a configuration problem, either with a Windows machine running SQL Developer, or with my SQL Developer. Where should I look for further research?

@Devolus's answer to the Procedure for exporting a table to several csv files includes the instruction "In the SQL Window, right-click → Change window in → Command window", but if I right-click on the SQL window, I do not see the parameter " Change window. "

(running Windows 7, SQL Developer version 4.0.2.15, build 15.21, database - Oracle 11.2)

Answer 1

The fact that the file was created but does not have data, possibly the last statement, SPOOL OFF has not yet been completed. Add a new line to the script and try again.

For example, your script would look like this:

  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 

Answer 2

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

He fixed my problem, hope he can fix yours too

Answer 3

In my case, it took two things to fix the problem:

• runs from a script (using @C: \ file.sql notation)
• upgrade to the latest version of SQL Developer

I ran SQL Developer 3.2.20.09 (does not work), and now I use 4.0.2.51 (the same script works).

Besides,

Answer 4

My problem was solved after I selected the whole script and hit F5 in the SQL developer.