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:


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:

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


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


  1. Avatar

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

  2. Avatar
    Colin D HOBBY Reply

    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?

    • thatjeffsmith

      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.

  3. Avatar

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

    • thatjeffsmith

      1. Almost. You can wrap your query with a

      spool c:\users\jdsmith\DATA.csv
      SELECT /*csv*/ * FROM employees
      spool off

      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.

    • Avatar

      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

    • thatjeffsmith

      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

  4. Avatar

    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

  5. Avatar

    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!


    • Avatar

      Did you get to know any such meta tag. That would be of great help.

  6. Avatar

    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?

    • Avatar

      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;

    • Avatar

      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.

  7. Avatar
    Kishore Kumar Enumula Reply


    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.


  8. Avatar
    peter wang Reply

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

  9. Avatar

    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

  10. Avatar

    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.

    • thatjeffsmith

      1. Use a DB_LINK
      2. No, but you can use MINUS queries to see what rows are in one set vs another

    • Avatar

      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 ?

  11. Avatar

    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?


    • thatjeffsmith

      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.

  12. Avatar
    michael nerenburg Reply

    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?

    • thatjeffsmith

      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

  13. Avatar

    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 .

    • thatjeffsmith

      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.

  14. Avatar
    Eric Miller Reply

    Jeff, thank you for the help. I’ll be looking up dynamic SQL next!

  15. Avatar

    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?

    • thatjeffsmith

      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.

  16. Avatar

    Jeff, is it possible to run the same query on multiple servers from one window?

Write A Comment