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.

thatjeffsmith
Author

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

10 Comments

  1. 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

    • thatjeffsmith

      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?

  2. 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

    • thatjeffsmith

      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

    • thatjeffsmith

      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)?

  3. 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

Write A Comment