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.

  1. right-clicking on a result grid
  2. spooling to a file

Grid Exports

The ubiquitous data grid!

Spooling

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.

THIS

What happens next?

You get a 2 screen wizard. The first screen looks like this:

Here comes trick #1

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.

This one!

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.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

7 Comments

  1. Doug Smith Reply

    When using the spool command with a CTE I am running into errors at line number of the beginning of the CTE stating incorrect syntax near “spool” even though the line number for spool is at the end of the CTE. I am looking to run the query once in the CTE and then break apart the results multiple times into different files based on criteria.

    I can’t get even the 1st spool to work when using a CTE but this is what I am working with after the CTE

    SPOOL C:\Download\Core_Upload\ATLANTA.csv
    SELECT /*csv*/
    cte.product,
    cte.new_core
    from cte
    WHERE account = ‘ATLANTA’
    spool off;

    SPOOL C:\Download\Core_Upload\CHARLOTTE.csv
    SELECT /*csv*/
    cte.product,
    cte.new_core
    from cte
    WHERE account = ‘CHARLOTTE’
    spool off;

    • Doug Smith

      Common Table Expression, so the temporary result of my query or the result of my WITH statement.

      Looking at your beers example, is it possible to spool the results of another query to another file without running the strong_beers part again. So spool your high ratings to good_strong_beers and then low ratings to bad_strong_beers?

    • thatjeffsmith

      >>without running the strong_beers part again.
      the WITH clause is only valid per query…there is no temp table that remains for another query to run…if you want that, create an actual TEMP table

      but this works

      SET sqlformat csv
      spool c:\users\jdsmith\desktop\strong_beers.csv
      WITH strong_beers AS (SELECT *
                              FROM untappd
                             WHERE beer_abv > 10
      )
      SELECT *
        FROM strong_beers
       WHERE rating_score > 4;
       
      spool off
       
      spool c:\users\jdsmith\desktop\weak_beers.csv
      WITH strong_beers AS (SELECT *
                              FROM untappd
                             WHERE beer_abv < 6
      )
      SELECT *
        FROM strong_beers
       WHERE rating_score > 4;

      run with F5 – out pops 2 CSV files

    • Doug Smith

      I figured it was wishful thinking – thanks for your help as always!

  2. Hi Jeff

    why do I see the query as well in the downloaded CSV file? and how to avoid this?
    Arun

    • thatjeffsmith

      You haven’t shown me how you’re generating the file, but I’m going to guess you’re spooling.

      Use this in your script
      SET ECHO OFF

Write A Comment