26

Using SQL Developer to run queries works good, but I would save a lot of time if I instead of first running the query and then right click the result set and go through the export to csv routine.

I was wondering whether it is a way in SQL Developer to: 1) Write the query, and then select that the result of the query should be exported to disk. 2) Write a queue of several queries, each of them writing their results to disk.

olovholm
  • 1,362
  • 5
  • 16
  • 23
  • Possible duplicate: http://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer – MackM Dec 10 '14 at 20:37

3 Answers3

44

You can use the spool command (SQL*Plus documentation, but one of many such commands SQL Developer also supports) to write results straight to disk. Each spool can change the file that's being written to, so you can have several queries writing to different files just by putting spool commands between them:

spool "\path\to\spool1.txt"

select /*csv*/ * from employees;

spool "\path\to\spool2.txt"

select /*csv*/ * from locations;

spool off;

You'd need to run this as a script (F5, or the second button on the command bar above the SQL Worksheet). You might also want to explore some of the formatting options and the set command, though some of those do not translate to SQL Developer.

Since you mentioned CSV in the title I've included a SQL Developer-specific hint that does that formatting for you.

A downside though is that SQL Developer includes the query in the spool file, which you can avoid by having the commands and queries in a script file that you then run as a script.

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    The query returns the SQL in the CSV as well. Can you remove this? – Dan KS Jun 11 '18 at 17:40
  • 1
    @DanKS - I referred to that in the last paragraph. But the behaviour has changed in recent releases of SQL Developer, I think from 4.1 or 4.2 maybe, but might have been later. I've just checked on 18.1 and it does not include the query any more, so upgrading to the current version should help. You may want to add an explicit `spool off` before starting a new file though, otherwise the second spool command itself appears in the first file. – Alex Poole Jun 11 '18 at 17:47
  • Apologize for missing that and I'll take a look at the at my current version number. – Dan KS Jun 11 '18 at 18:00
4

Click in the grid so it has focus.

Ctrl+End

This will force the rest of the records back into the grid.

All credit to http://www.thatjeffsmith.com/archive/2012/03/how-to-export-sql-developer-query-results-without-re-running-the-query/

SANDY MENA
  • 49
  • 1
  • 2
    No need for Ctrl + End, if you have searched first rows and select export from grid, then exported file would contain all rows (query would be rerun). This can be big performance difference in case of large data sets as there will be no need to display it in SQL Developer. – andro83 May 05 '15 at 14:13
2

After Ctrl+End, you can do the Ctrl+A to select all in the buffer and then paste into Excel. Excel even put each Oracle column into its own column instead of squishing the whole row into one column. Nice..

Vijay Kumar
  • 841
  • 1
  • 9
  • 13