Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Tutorial: Exporting Query Results

3 Mins Read

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.

Author

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

Related Posts

23 Comments

  1. Alex Reply

    Hi Jeff, when exporting to .xlsx or .csv is it possible to change the font the data is exported in?

  2. Sanjeev Kumar Reply

    Hi Jeff,

    Hope you are well. I have been trying to export the result of a query. However I seem to get the following stack trace:
    java.lang.NullPointerException
    at java.io.File.(File.java:277)
    at oracle.dbtools.raptor.export.ExportGenerationOutputStream.closeCurrentStream(ExportGenerationOutputStream.java:537)
    at oracle.dbtools.raptor.export.ExportGenerationOutputStream.finished(ExportGenerationOutputStream.java:464)
    at oracle.dbtools.raptor.export.ExportAPI$1.doWork(ExportAPI.java:1490)
    at oracle.dbtools.raptor.export.ExportAPI$1.doWork(ExportAPI.java:1453)
    at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

    The actual query that I am trying to run is about 660KB in size. I tried to reproduce the issue with the different query – shown below. The version of SQL developer is v18.4 . I was able to reproduce on v20.2 as well.

    select sysdate, case when (1 = 2 or 1 = 2 or …. 1 = 2) THEN ‘EQUAL’ ELSE ‘NOT EQUAL’ end as CASE_VALUE from dual;

    Around the time this SQL query reaches 8260-8280 bytes in size. The export from SQL developer fails with the above stack trace.

    • Sanjeev Kumar

      Thanks Jeff – I was able to export successfully on V20.4. Cheers!

  3. John Laidlaw Reply

    Is there a way to generate the results to multiple worksheets based on a column in the query?

    So if the query returns:
    select b.Brewery,b.Brand
    from Brewers B, Locations c
    where b.location = c.location
    and c.country = ‘CANADA’;

    Brewery Brand
    Oskar Blues, Rose for Daze
    Oskar Blues, Dale’s Pale Ale
    Oskar Blues, G’Knight
    Labatts, Blue
    Labatts, 50
    Labatts, Crystal
    Labatts, Genuine Draft
    Big Rock Traditional
    Big Rock Grasshopper
    Big Rock White Rabbit
    Toolshed Red Rage
    Toolshed Star Cheek
    Toolshed Purple Haze
    Toolshed Flat Cap

    Is there some way to get that output so each Brewery would have its own worksheet (i.e. a worksheet per unique brewery)?

    • thatjeffsmith Post Author

      The best i can offer you is Views.

      Create a view for each worksheet you want. Then use the database export to export those views to a single Excel file. Each query result (view) will go to its own worksheet.

  4. Luis Orfão Reply

    iI have the same problem.
    When exporting to xlsx, using SQL DEV 9.2.0, only de first row is exported.

    Any ideia when patch will be available?
    Thanks!

  5. Harald Reply

    After a recent update to SQL Developer Version 19.2.0.206, Build 206.2117 the export to Excel only returns one (1) row of data. The earlier version of SQL Developer export returned the full query result. Is it a bug or have I missed some settings?

    • Gerhard

      I’ve the same problem with 19.2.0.206, Build 206.2117.
      With Version 19.1.0.094 Build 094.2042 the excel-Export works as excpected.

      It would be fine to know if the problem can be solved by settings or if it’s a bug.

    • Michiel

      Same thing happened here, exact same version and build of SQL Developer. In our case it turned out that the first row contained a date field with a null-value. For some reason this makes the export to Excel stop at that point. Still looking for a solution.

    • thatjeffsmith Post Author

      The solution is to use CSV in 19.2, or go back to 19.1, or wait for us to patch release 19.2.1 (which I hope will be next week).

  6. Venkat Reply

    Hi Jeff. If the records need to be exported to Excel and multiple table results needed to pushed as different sheets within the same excel – is there a code option for this ? How to achieve this ?

  7. 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 Post Author

      >>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!

  8. Arun Reply

    Hi Jeff

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

    • thatjeffsmith Post Author

      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