There are two methods for running queries in SQL Developer:

Run Statement

Run Script

What’s the Difference?

There are some obvious differences between the two features, the most obvious being the format of the output delivered. But there are some other, more subtle differences here, primarily around fetching. What is Fetch?

After you run send your query to Oracle, it has to do 3 things:

• Parse
• Execute
• Fetch

Technically it has to do at least 2 things, and sometimes only 1. But, to get the data back to the user, the fetch must occur. If you have a 10 row query or a 1,000,000 row query, this can mean 1 or many fetches in groups of records.

Ok, before I went on the Fetch tangent, I said there were two ways to run statements in SQL Developer:

Run Statement

Run statement brings your query results to a grid with a single fetch. The user sees 50, 100, 500, etc rows come back, but SQL Developer and the database know that there are more rows waiting to be retrieved. The process on the server that was used to execute the query is still hanging around too. To alleviate this, increase your fetch size to 500. Every query ran will come back with the first 500 rows, and rows will be continued to be fetched in 500 row increments. You’ll then see most of your ad hoc queries complete with a single fetch.

Run Script

Run Script runs the contents of the worksheet (or what’s highlighted) as a ‘script.’ What does that mean exactly? Think of this as being equivalent to running this in SQL*Plus:

@my_script.sql;

Each statement is executed. Also, ALL rows are fetched. So once it’s finished executing, there are no open cursors left around. The more obvious difference here is that the output comes back formatted as plain old text.

The Trick: Run Statement Works With Multiple Statements!

It says ‘run statement,’ but if you select more than one with your mouse and hit the button – it will run each and throw the results to 1 grid for each statement.

This will work regardless of what you have this preference set to:
DATABASE – WORKSHEET – SHOW QUERY RESULTS IN NEW TABS

Mind the fetch though! Close those cursors by bring back all the records or closing the grids when you’re done with them.