Running Multiple Queries in Oracle SQL Developer

thatjeffsmith SQL Developer 24 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 24

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

  2. 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?

    1. 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;

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

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

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

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

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

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

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

      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

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

    1. thatjeffsmith Post
      Author

      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.

  10. 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?

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

Leave a Reply

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