Running Multiple Queries in Oracle SQL Developer
- by thatjeffsmith
There are two methods for running queries in SQL Developer:
Run Statement
Run Statement, Shift+Enter, F9, or this button
Run Script
No grids, just script (SQL*Plus like) ouput is fine, thank you very much!
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.
Scroll down, or hit Ctrl+End to force a full fetch and get all your rows back.
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.
Run one or more commands plus SQL*Plus commands like SET and SPOOL
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.
If you mouse hover over the Query Result panel tab, SQL Developer will tell you the query used to populate that grid.
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.
