Scripts – on Spooling and Output to Screen in Oracle SQL Developer

thatjeffsmith SQL Developer 4 Comments

Tell Others About This Story:

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.

Related Posts

Tell Others About This Story:

Comments 4

  1. thatjeffsmith Post
    Author
  2. 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

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *