Is there anyway to export the results of a query in Oracle SQL Developer without having to execute the query twice? Running a SQL query in worksheet, then Right click on Result Set window -> Export Data -> Text will run the query a second time.
3 Answers 3
There are some SQL Developer specific comments/"hints". For example if you run the below as a script (F5) and not a statement (Ctrl-Enter):
select /*csv*/ * from table;
You will get the results in CSV format the first time already. You can even spool the output just as in SQL*Plus. So you could just run the below block of code as a script (select lines and F5) and get a CSV directly in one pass:
spool C:\Users\XYZ\Desktop\my.csv
select /*csv*/ * from table;
spool off
Starting with version 4.1, you do not even need to use the above comment/"hint". You can just:
SET SQLFORMAT csv
Then run your query as a script.
Further options here: http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/
Yes, but it's expensive. If you fetch all the rows down to the client, then the export will use that recordset for the export.
If it's a long running query of a few rows, no big deal. If it's a long running query of millions+ of records, you might exhaust the JVM or upset the network guy/gal.
I talk about this more here.
TL/DR;
Scroll to the end of the dataset in the grid, once all the rows are there (no more to fetch), if you do an export, the data will come from there vs executing the query again.
Use sqlplus. Develop the scripts in Sql Developer then load then with sqlplus.