CODEWITHSUNDEEP
oracle-databaseoracle11goracle-sqldeveloper
JamesF
JamesF

Reputation: 449

SPOOL returns empty files when trying to export from SQL Developer

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)

Upvotes: 14

Views: 22358

Answers (5)

Manideep Kumar
Manideep Kumar

Reputation: 1

Wonderful,my problem solved , First i cleared all queries in sql developer page window (where i am running the queries) and kept only the spool comand which i want to run after selection (ctrl+A) and clicking F5, ran and content also got generated in a file,where initially only empty file alone getting generated

Upvotes: 0

Osama Al-Banna
Osama Al-Banna

Reputation: 1515

My problem solved after I highlight the whole script and press F5 in SQL developer .

Upvotes: 4

deccles
deccles

Reputation: 111

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

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

I was running SQL Developer 3.2.20.09 (doesn't work), and now use 4.0.2.51 (exact same script works).

Also,

Upvotes: 1

Keny
Keny

Reputation: 196

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

Upvotes: 17

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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

Upvotes: 7

Related Questions