This is a 101 level type of topic – a fundamental bit of SQL Developer that I would encourage everyone to be familiar with. Why? Because it’s probably something you’ll be doing, A LOT. So let’s get into it, already!
You have two options for getting data from a query into a FILE.
- right-clicking on a result grid
- spooling to a file
Exporting Data from a Grid
Here’s what happens.
You’re executing a query, browsing a TABLE, or running a report.
You see a GRID.
You right-click on it.
What happens next?
You get a 2 screen wizard. The first screen looks like this:
Trick #1: You’re going to be doing this, A LOT. So, go into preferences and setup your defaults. You can see by my screenshot that I’m most frequently doing CSV exports directly to my clipboard.
Once you’ve set what you want, and click NEXT, you review what you’ve asked for and say FINISH.
But then what happens?
WE EXECUTE THE QUERY AGAIN. Grab the results, format them based Wizard settings, and feed them to the destination you’ve selected.
So, if you have an extremely long running query, and you don’t want the query to be executed again to do an Export, then you need to do Trick #2.
Trick #3: If you’re dealing with a large amount of records and are exporting to Excel, always choose the XLSX format (we list it first). If you choose XLS instead, stubbornly, it’s going to peter out after a hundred thousand rows or so…if you do not see the XLSX format option, that means YOU NEED TO UPGRADE SQL DEVELOPER.
Trick #4: If you have BLOBs in your query resultset, the ONLY way to export those out successfully is to use the Loader format option.
Exporting Data from a Script
Once you’ve ran the script and are looking at the results, the only way to get those into a file is to copy/paste.
So, what you need to do is tinker with your script, and have it use the SPOOL command.
SPOOL is a SQL*Plus feature that we support in SQL Developer. You have access to this command and other script processing bits when you use THIS button (or F5) to execute something.
We do better than just support the SPOOL command. We have built some additional commands to make working with FILES much easier.
Let’s look at this ‘script’:
clear screen -- clears the output screen SET pagesize 300 -- every 300 rows, print the column headers SET sqlformat csv -- spit the results out in a comma separated values format cd c:\users\jdsmith -- change the current working directory spool employees.csv -- capture everything that happens next to this file SELECT * FROM employees; -- get me the employees spool off -- stop writing to that file !TYPE employees.csv -- run this OS command (windows CAT of a file basically)
The ‘cool’ parts of this script are:
And that’s it. Well, that’s ALMOST it. If you have a VERY LARGE result-set, writing those query results to the screen AND to the file at the same time is going to be a major BUMMER.
You’re going to want to tell the script engine to SUPPRESS the query results to the screen. That’s done via the SET TERMOUT OFF, but beware, this is only honored when it’s executed inside a @script_file – not interactively in SQL*Plus or via a SQL Worksheet Editor window in SQL Developer.
And now you know, the rest of the story.