When executing scripts in SQL Developer, the amount of output we display on the screen is LIMITED.

By design, we only show you 5,000 records from any query, and we limit the amount of output in total for a single script execution to 10,010 rows.

This is controlled here:

These are the defaults.

I know what you’re thinking…

Why?

A few reasons, but practicality is what it boils down to. Displaying 3M rows of script output in a GUI is not going to be fun for you or for the application. That’s a lot of machine resources to consume, and it’s more than likely to exhaust the memory allocated to the JVM. Plus, are you really going to sit there and scroll through that many rows of output?

Still, we do offer the preferences, and you’re more than welcome to increase them.

What Happens When we Hit the Limit?

Let’s say we want to build a CSV for excel. We have more than 5,000 records. Let’s just do it anyway.

SET sqlformat csv
spool c:\objects_data.csv
SELECT * FROM all_objects;
spool off

Let’s observe the output.

So the query ran, we continued to fetch the results, we just didn’t print them to the screen.

And the ‘Excel’ file?

73,000+ records. No problems.

A Few Tips for Faster Scripts

Write your script to a .SQL file.

Run it this way.

Don’t open the file, and collapse the script output panel.

This will allow the script to run faster, or at least allow SQLDev to handle the script execution as fast as possible, instead of trying to advance the script in the editor and page through the results in the open display panel.

If you want it to run even faster, use SQLcl, and use a shell/batch script to run it…and add this line to your script

SET TERMOUT OFF

SET TERMOUT OFF isn’t observed in interactive mode ONLY when embedded as part of a script being @ executed.

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.

23 Comments

  1. Arnab modak Reply

    Hi sir while trying to export table to excel file can we give cell reference of excel file inside spool?

  2. When trying this approach for a fairly straightforward query, I still get

    Only 5,000 rows currently supported in a script results
    5,000 rows selected

    Is there a way to get all results spooled to the file? And also not echo the results to the Script Output tab?

    • All results are always spooled to the file – the limit is only for what shows in the script output panel.

      If you don’t want to see script results but you do want to spool, you need to use the set termout off

  3. Hi, Jeff!

    I’ve read all your posts about spooling and finally made what I wanted – separate spooling for several queries on for-loop. But there is one small problem which ruins everything (for non-latin languages): spooling creates csv-file in UTF-8 encoding without BOM-characters, so when I try to open this csv-file via excel by doubleclicking – Excel doesn’t recogninze it as UTF-8 and all non-latin characters are turning into mess.
    Is there any way to enable BOM-character? Or change encoding of spooling?
    (I use SQL Developer 18.4.0.376)

    Simplified example to reproduce case:

    spool c:\temp\testfile.csv
    SET SQLFORMAT DELIMITED ; ” ”
    select ‘Cyrillic column name’ as “Кириллическая шапка”, ‘Кириллица’ as “Cyrillic”
    from dual;
    spool off;

    • I don’t know what I don’t know here…for example, why does VS Code open the file with no problems? Seems like a problem with Excel to me, as the data appears correctly when looking at it in my bash terminal, SQLDev UI, and various text editors.

  4. Hi Jeff,

    What is needed to spool a file directly to a sharepoint location? I presume I have to add code to be able to login to sharepoint?

    Query used:
    column filename new_val filename
    select ‘https://[hidden].sharepoint.com/sites/P2P_Reporting_Analyse/test.csv’ filename from dual;
    spool &&filename

    set sqlformat csv;

    select
    *
    from
    dual
    ;
    spool off

    result is:

    “FILENAME”
    “https://[hidden].sharepoint.com/sites/P2P_Reporting_Analyse/test.csv”

    SP2-0556: Invalid file name.
    “DUMMY”
    “X”

    not spooling currently

    I am using SQL Developer Version 19.2.1.247, build 247.2212

    • Can’t spool to a http address. Can you map your SharePoint destination to a network drive?

  5. Is there a reason SET TERM OFF is not supported in the worksheet? I’m a developer who has recently become involved in a lot of query tuning tasks. I would prefer to use a SQL Developer Worksheet for these tasks, but the lack of SET TERM OFF probably means that I need to move over to the command line for certain tasks. Why is running a script in a worksheet different than running a script in SQLCL? Maybe I’m just using the wrong tool for the job and I need to adapt to the command line. Or maybe, there’s a better approach in SQL Developer.

    To elaborate, here’s the type of task I’m referring to

    set termout off
    run big query
    set termout on
    display cursor infomation/stats of query that was just run

  6. Hello friends,
    How do i spool an SQL query output in xlsx format?
    I tried the below query and file got generated, but when I open the file, it throws error.

    set sqlfomat xlsx
    spool C:\Users\velusde\SORR-FDM.xlsx
    select * from knta_users;
    spool off

  7. Jeff, at the moment I just see the following running a script with more than 5000 rows returned in SQLcl 18.4:

    SEVERE: java.lang.StringBuffer.charAt(Unknown Source)
    java.lang.StringIndexOutOfBoundsException: String index out of range: 8
    at java.lang.StringBuffer.charAt(Unknown Source)

    • Hmmm, this is what I just tried

      SQL> SELECT substr(text, 0, 45) FROM all_source
        2* fetch FIRST 10000 ROWS ONLY;

      I got 10,000 rows back in a few seconds, also using SET SQLFORMAT ansiconsole.

  8. Hi Jeff,

    I am spooling a query which gives as a result more then 300K rows. Please see the comand below

    SET sqlformat csv
    spool C:\Users\ecm0354c\Downloads\roamers.csv
    select * from aeropuerto where substr(MSISDN,1,2) not in (’57’);
    spool off

    However, when I check the file generated it only has the following information:

    SQL> select * from aeropuerto where substr(MSISDN,1,2) not in (’57’)
    SQL> spool off

    Also, in the scriptoutput section only shows Query Run In:Query results. Does not show task results.

    Do you know why? What I am doing wrong?

    Thanks

    • SQLcl or SQL Developer?

      And the Version # for what you’re using?

      Does it work for ANY query – or is it just not working for this one?

  9. Hi Jeff,
    This feature is terrific for running existing sqlplus script and get nicer output.
    I came across 2 “issues” and hope to get your input:
    1. If I don’t specify the path for the spool file, where is the default location it is saved to?
    2. The html spool file produced has black headings by default. How can I change black to a more gentle color?
    Thank you!
    Vanha

    • a temp directory, which you can see if you run ‘show sqlpath’ or if you try to open a file in the worksheet – the starting directory

    • Hey Jeff,

      SQL Developer Ver. 4.1.4.21.21

      And it does not work for any query.

      Thanks for your help

    • 4.1 is 3+ years old, AND the scripting logic changed completely from version 4.1 to version 4.2 – can you upgrade to latest (18.4)?

  10. Hi.

    I have a question related to other “output area”. I came from PLSQL Developer and in that product when I run code using functions of HTP package “magically” a new tab for HTP output is shown.
    How can I do this magic in SQLDeveloper? Where can I see HTP output?

    Regards

Reply To Vanha Le Cancel Reply