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.
41 Comments
If I have two SELECT statements only in the worksheet and do ‘Run Script’ (F5) then those will be executed and printed out the results in ‘Script Output’ tab, second SELECT under the first one. When do same using ‘Run statement’ (F9) selecting both of them then I will get two ‘Query Result’ tabs one next to another. Is there any way to show those two one under another like it is shown with F5? For instance Microsoft SSMS does this in such a way.
Sorry, but No.
Hi Jeff,
Hope you are well. The development team at my company assist production support teams by testing queries on test before running it on production.
This is how they test on the lower environment:
1. Select statement to view current value that needs to be changed
2. Update statement to change the current value.
3. Select statement to view the new updated value.
4. Rollback Statement , so that test data can be used in the future.
The two select statements help them validate the update statement and it gets passed onto the Production Support team.
The team prefers to get the select output in a grid format and when we run it (F9 or play button) we see two query result grids. However we see that 75% of the time statement no. 3, does not show the updated value and shows the rolledback value – even though rollback follows statement 3 as you can see from above. Just wanted to check why the result is different intermittently. We are on V18.4. Thanks for your support.
That version is 3 years old…
They being said, we’re showing what the database gives us.
I would need more info to hazard a guess what might be happening here.
Hi Jeff,
Thanks for your note. I tried running this on V20.4. It seems to show the same behavior. There is one select followed by an insert statement, 6 update queries and then the same select once again – followed by rollback. The entire script is run by selecting all (Ctrl + a) and then (Ctrl + Enter)
SELECT account_number,
id,
next_index_due_date,
next_contrib_due_date,
pending,
letter,
last_updated,
last_updated_by
FROM payment det
JOIN client_acct_rltnshp ON carb_id = car_id
JOIN client ON client_id = clnt_id
JOIN account_link
ON account_id = aclk_id
AND aclk_account_number IN (
‘a1’,
‘a2’,
‘a3’,
‘a4’,
‘a5’,
‘a6’
)
JOIN code ON code_id = stat_code_id
WHERE stat_code_id = 47
AND required = ‘Y’
ORDER BY 1, 4 ASC;
INSERT INTO map_payment
SELECT *
FROM payment
WHERE id IN (
n1,
n2,
n3,
n4,
n5,
n6
);
UPDATE payment
SET next_index_due_date = TO_TIMESTAMP (’18/10/2021′, ‘DD/MM/YYYY’),
last_updated = SYSDATE,
last_updated_by =
‘REF ‘ || TO_CHAR (SYSDATE, ‘DD/MM/YY’)
WHERE id IN (n1);
UPDATE payment_detail
SET next_index_due_date = TO_TIMESTAMP (’18/04/2022′, ‘DD/MM/YYYY’),
last_updated = SYSDATE,
last_updated_by =
‘REF ‘ || TO_CHAR (SYSDATE, ‘DD/MM/YY’)
WHERE id IN (n2);
UPDATE payment
SET next_index_due_date = TO_TIMESTAMP (’27/04/2022′, ‘DD/MM/YYYY’),
last_updated = SYSDATE,
last_updated_by =
‘REF ‘ || TO_CHAR (SYSDATE, ‘DD/MM/YY’)
WHERE id IN (n3);
UPDATE payment
SET next_index_due_date = TO_TIMESTAMP (’11/09/2021′, ‘DD/MM/YYYY’),
last_updated = SYSDATE,
last_updated_by =
‘REF ‘ || TO_CHAR (SYSDATE, ‘DD/MM/YY’)
WHERE id IN (n4);
UPDATE payment
SET next_index_due_date = TO_TIMESTAMP (’22/04/2022′, ‘DD/MM/YYYY’),
last_updated = SYSDATE,
last_updated_by =
‘REF ‘ || TO_CHAR (SYSDATE, ‘DD/MM/YY’)
WHERE id IN (n5);
UPDATE payment
SET next_index_due_date = TO_TIMESTAMP (’29/04/2022′, ‘DD/MM/YYYY’),
last_updated = SYSDATE,
last_updated_by =
‘REF ‘ || TO_CHAR (SYSDATE, ‘DD/MM/YY’)
WHERE id IN (n6);
SELECT account_number,
id,
next_index_due_date,
next_contrib_due_date,
cpi_pending,
letter,
last_updated,
last_updated_by
FROM payment det
JOIN client_acct_rltnshp ON carb_id = car_id
JOIN client ON client_id = clnt_id
JOIN account_link
ON account_id = aclk_id
AND aclk_account_number IN (
‘a1’,
‘a2’,
‘a3’,
‘a4’,
‘a5’,
‘a6’
)
JOIN code ON code_id = stat_code_id
WHERE stat_code_id = 47
AND required = ‘Y’
ORDER BY 1, 4 ASC;
rollback;
The The script output shows as below:
6 rows inserted.
1 row updated.
1 row updated.
1 row updated.
1 row updated.
1 row updated.
1 row updated.
>>Query Run In:Query Result 1
Rollback complete.
The select statement at the end seems to show the rolled back state of the table even though the select happens before the rollback. Also as stated previously, the behavior is intermittent.
Yeah, don’t do this. Run that as a script, F5, or one thing at at time.
If they were all just SELECTs, what you are doing would probably be OK.
Hi, Jeff.
Thank you for interesting article. But please tell me, how can I run actualy multiple queries, I mean in parallel? I have a set of reports, placing in sql files. Usualy I run one report in time, but somewhen (for example in the end of month) I need run many of them and still need work with SQL Developer in the same time (Schema browser, worksheets, SQL Monitor etc).
You’ll need one unshared worksheet for each query you want to run.
Jeff,
If I have one statement running and open another worksheet and try to run it, it locks up my whole session.
I know about the unshared worksheets and how you can only run one statement per connection, but is there a way to unlock the sql dev application so I can at least copy or save work before having to kill the session?
A busy connection shouldn’t lock the UI, prevent a file from being saved.
I’m out of the office until Friday, will take a look then.
I know this question is from 4 years ago, but it is still relevant today. Firstly, I have been using sql developer for many many years now and greatly appreciate the work that has gone into it over the years. My thanks to all involved.
As per the question about the UI locking from running more than one query against the same db session , this to my knowledge has *always* caused the UI to “apparently” lock and is easily demonstrated: run a query (that will execute over many minutes before returning) using a database connection. Try to do anything else in sql developer using that same database connection (e.g. query the database in a separate (shared connection) window, or try to inspect database tables, views or other objects on that same connection in the db “connections” panel. The UI will then lock and remain non-responsive until the query completes. I say “apparently” locked because it hasn’t frozen as such, it is just waiting to be able to continue, and will eventually return. It is however unresponsive to any user interaction in the meanwhile.
The OP asked how you can save your work before killing the session, so I’m adding this for anybody else arriving here as I suspect that 4 years is a long time to wait to find out how to save your work 😉
Now… if by “killing the session” they meant physically killing the database session (e.g. alter system kill session…. ), then in theory once the db session is “killed”, the SQL Developer app would normally “unlock” and then it would be possible to go about life without losing any work, as SQL Developer hasn’t actually frozen or crashed, it normally just becomes unresponsive while waiting for the query to return, and it will “return” when the db session is killed. Thus if that is the question, killing the (database) session should not mean you cannot subsequently save your work.
Alternatively you can simply wait for the database actions to complete (e.g. by drinking a coffee or – 4 years on – asking chatGPT to write your next Oracle Stored Proc for you).
But, sometimes killing the session isn’t an easy option. I have worked in many corporate environments in recent years where getting hold of a dba at short notice to deal with killing a session is a non-trivial exercise, and almost nobody gives us “mere” developers access to v$session or “kill” functionality anymore without us having to sign away rights to our first-born, and even then they say no!
If you don’t care about saving your work (or are one of those strange people who actually remember to save your work), you can of course kill SQL Developer in Task Manager, and leave Oracle to clear up the mess. But, even if you are one of the aforementioned “strange people”, this can still come at a price, as you will have lost any recent information in Sql Developer about current open files or possibly config changes because I have come to realise it only saves such information when it cleanly exits.
So what do you do if you don’t have time to wait, cannot easily kill the db session, don’t want to lose latest config changes (or your work) by killing sql developer, but still want a solution to getting on with your life?
There is a simple solution, but this goes with the caveat that before using this solution, make sure you aren’t causing yourself additional problems with anything else you are doing. Ensure that any other apps you have which are using your network connection are closed down safely if necessary and that you have saved anything else you are working on… and then…. well… chop your network connection!
Yes… that’s the solution I found works for me in that situation… e.g. temporarily remove the lan cable , switch off your wifi connection, or disconnect your vpn connection… whatever it takes. Once disconnected from the network, the current db connection is lost and of course, depending on what was executing on the databse, there may be some remedial action or rerunning required, but sql developer should realise that it has received a database disconnection error and return to its normal responsive state., whereupon you can re-attach your network, save your work, and get on with your life. 🙂
Love your tutorials Jeff.
Q1: In Teradata there was an option to have the results automatically exported to excel when the query completed. Is there any way to do this in SQL Developer (currently using 4.0.0.13 but could be upgrading).
Q2: Can I make the Run Statement fetch all the results at once? It looks like Run Script will Fetch all rows but I can’t seem to Export the results of Run Script to Excel like I can in Run Statement. (Or am I missing something?)
1. Almost. You can wrap your query with a
Run that with F5, and it will spit out a file you can open with Excel.
Almost. if you run your query, and then in your grid, hit Ctrl+End, it will fetch all the rows to the grid. Warning, this can be expensive.
Thank you for the prompt answer. But to follow up, I just tried your Spool suggestion but the results are returned in a single column when opened in Excel. Is there a way to create a file that Excel will be able to read as separate columns? I tried set SQLFORMAT delimited, I thought that would insert commas but it didn’t. (I tried xls too, just in case. But no luck)
spool C:\Users\H5555\Desktop\enctrCV.csv
SET SQLFORMAT delimited
SQLPLUS Command Skipped: set SQLFORMAT delimited
you can’t spool xls…i i spool csv to file it works for me
i think the problem is your version, 4.0.0 – is very old, i’m on a version that bout 4 years newer than that (18.4) – please upgrade and you should be fine
Thanks Jeff for great tips.
i have a question on how to run SQL Query and PL SQL block both in SQL work sheet. ? Please let me know
Can you be more specific?
Love Sql developer.
I just barely discovered that you could change the name of a query result which helps me a lot. Is there some kind of meta tag I can give an individual query that would automatically name the result for me? So if i had :
Select query1 ; — First Query
Select query2 ;– Product Result
Select query3 ;– Export to excel
Then it would name each result set returned by those queries what I placed after the meta tag.
If there isn’t a way to do this, It might be useful.
Thanks for a great product!
Mark.
Did you get to know any such meta tag. That would be of great help.
I love this ability to run multiple queries at once and receive separate resultsets. I have a fairly complicated query that I’d like to separate into 4 results more easily than running it 4 times.
What I’ve tried is something like this:
with student_data as (
//the complicated bits
)
select * from student_data where grade=9;
select * from student_data where grade=10;
select * from student_data where grade=11;
select * from student_data where grade=12;
It works for the first grade_level, but the second query doesn’t know about the table created using the WITH clause at the beginning. The semicolon must finish off the transaction and move along.
Unfortunately, we don’t have CREATE privileges on the database, so I can’t do a temporary table. Any other ideas?
The WITH clause doesn’t create a table, it defines a “common table expression” which is kind of like a private view for one query only. In your case you could do something like this instead:
with student_data as (
//the complicated bits
)
select * from student_data
where grade IN (9,10,11,12)
ORDER BY grade;
You’re right. I was using the word “table” in the familiar sense, not that it creates an actual table in the database.
I would really love to get the results in 4 different sets. The complicated bits take a few seconds to run, and I’m pasting the results into 4 tabs of an Excel workbook to run some statistics and dress up the output. If the WITH (actually several WITHs chained together) only runs once and lets me select from it four times, I’d save a bit of time in my current process. It’s really more of an academic question than anything since we’re talking only a very small time savings. I feel like what I want to do requires actually creating a temporary table that gets destroyed at the end.
Eventually, this will have a web-based front end where users can query the data themselves, and the GUI will do the dressing up that I’m currently completing in Excel. While I go through these rounds of mockups, I’m running things manually and seeing if they like the output.
Hi,
Is there any way to set SQl developer to “NOT UPDATE” mode. We are usually running Select queries but I need to restrict UPDATE,INSERT and DELETE statements automatically. Can you please suggest me if there any option like this.
Thanks,
Kishore
Create a user with connect and select privs only.
How to make a child report in style of ‘Script’, rather than the default ‘Table’? Tried to export the XML file and then edit, however, it generates error message such as ‘SQL Error: Invalid parameter index 1.’
Here are some background info: I am developing a data model browser in SQL Developer using the reports feature. The data model is defined in a data modeler system. The browser accesses the repository of the data mdeler directly and it is based on a SQL Server. The parent query of the browser generates a list of tables defininition in a data model, and the child reports will include the column details (in default table style), the constraints detail (in default table style), and the DDL (sql) that I would like to make it a script style, so that I can include mutiple statements in the child report query instead of using the union to combine multiple statements (order of each statement output is not allowed and using in-line table is not possible either for some queries due to limitation of T-SQL).
Q1) I have a set of select Queries, which i usually run them individually and copy the result to excel sheet. Is there any way to run all the quires at a time and get the result individually?
Q2)if so, Is there any way to export the result of each select query is automatically to excel sheet.
Q3)if not possible any procedure or script can help regarding this automation.
Any near by solution is welcomed.
Thank you
Hi Jeff,
Thank you for the helpful article.
I have questions,
1. I need to query from production and development database in a single query ? How can we achieve this ?
2. Can SQL Developer do the comparison between the table’s rows of Production and Development database ?
Thank you very much.
Ankur
1. Use a DB_LINK
2. No, but you can use MINUS queries to see what rows are in one set vs another
Thank you for the information.
Could you please tell me where exactly I can write Create dblink statement. like in production database connection’s worksheet ?
or what are the standard steps for it ?
Thatjeffsmith, this post help me a lot. I got question.
How can I export all output in one excel file with multiple sheet. (each table on eache sheet). Is it possible?
Thanks,
Khairi
No way to do this today…or if it’s just tables, try the cart, add multiple tables, and export to a single XLSX file and see if that works.
Thank you. I will try that.
3 Questions
1) So I can’t run multiple queries at once? It seems to wait until I am finished with one query before the second kicks off, it would be nice to run stuff in tandem
2) If I have a query with multiple selects, Sybase IQ will give me the results of each query, can oracle SQL developer do that?
3) How can I set a variable to so that when I run a query it doesn’t ask for the value to be entered?
1)You can, but not in a single worksheet. To run 3 queries concurrently, you’d need 3 dedicated connections. So open worksheet, execute query. Now open an unshared worksheet, execute query. Repeat as necessary.
2)Oracle queries only return a single resultset. If you have a stored procedure that returns multiple cursors, then yess.
3)Use this in your script, SET SCAN OFF
Hi jeff,everyday i have to execute lots of update and delete command in same schema in same window where i wrote it.usually i use f5 to run a particular query by selecting them.I am afraid that sometime I press f5 without selecting any query and it ran every query under that window,so if there is any unnecessary DML or DDL command then it will be a problem for me.Is there any option (other than commenting the query ) to disable the feature of running any query without selecting it .
There’s no option available, but there are some practical steps you can take to prevent this. When you’re doing your production work, always open a new worksheet and do it there. When you’re done, close it. Having a worksheet with ad hoc queries, inserts, creates, etc. could be a recipe for disaster – and it sounds like you’re already too familiar with this.
Jeff, thank you for the help. I’ll be looking up dynamic SQL next!
I would like to run the same query on multiple tables. Is it possible to do this in a more elegant and less error prone way than writing the same code multiple times?
It’s possible, but you’d have to code it in an anonymous block using dynamic SQL. We don’t have a IDE/GUI solution for you to do this today.
Jeff, is it possible to run the same query on multiple servers from one window?
Not using SQL Developer Boris. At least, not yet. That would be a very powerful (and dangerous) feature. Why not let your fellow users vote it up or down on our Exchange?