4 Replies Latest reply on Apr 3, 2017 10:39 PM by Gary Graham-Oracle

    reuse query results tab when using DEFINE statements


      I'm trying to parameterize the FROM and SELECT clauses in some scripts that I use, and run them from SQL Developer, but getting the output in the worksheet grid to make it easier to read, not on the script output window. A simplified query:



      DEF FROM=v$sqlstats

      SELECT *

      FROM &&FROM



      I have uncheck the following options:

      Database => Object Viewer => Automatically Freeze Object Viewer Windows

      Database => Worksheet => Show query results in new tabs


      However every time I run it using Ctrl+Enter, I get a new tab for the results. If I select just the query without the DEFINE statements, the same tab is reused.


      How can I make SQL Developer to reuse the same tab when I also include DEFINE statements?

        • 1. Re: reuse query results tab when using DEFINE statements

          You cant. the DEFINE bits are directly tied to the SQL*Plus engine we have - which means no grids.

          • 2. Re: reuse query results tab when using DEFINE statements
            Gary Graham-Oracle

            If you look carefully at the Script Output and Query Result tabs below the worksheet for your case, notice the following...

            1. You always get one unpinned Script Output tab that displays 3 things...

                a)  The "old" text of the SQL statement prior to DEF variable substitution

                b)  The "new" text of the SQL statement after DEF variable substitution

                c)  An indication of which Query Result tab the output of that SQL statement goes to, for example, >>Query Run In:Query Result

            2. One pinned Query Result tab for each SQL statment processed.


            In addition, you can see in Script Output for a script with multiple SQL statements (with or without variable substitution), the following...

            >>Query Run In:Query Result

            >>Query Run In:Query Result 1

            >>Query Run In:Query Result 2


            and so on, until the last statement.  It seems SQL Developer is treating repeated invocations of your script the same as if you merely replicated the script in the worksheet over and over again, one Query Result tab per SQL statement.


            If you wish to run multiple statements all in one go, that is the desired behavior.  However, since you wish to change the script between each invocation, this auto-pinning of the Query Result tab throws a monkey wrench into your model of how it should work. To my knowledge it has worked that way for many, many releases of SQL Developer, back to 2.x when Run Statement (Ctrl+Enter) stopped ignoring the DEF command and stopped automatically prompting for substitution variables in the UI.


            So it would seem your alternatives are...

            1. Close the Query Result tab between scxript invocations

            2. Just use an &FROM without the DEF command and let the UI prompt for the table name.

            • 3. Re: reuse query results tab when using DEFINE statements

              Thanks a lot for the clarification.

              You are rigtht. I get one unpinned Script Output with something like ">>Query Run In:Query Result 4", and one pinned query result for each SQL. I'm running only one SQL at a time.


              I'm not constantly changing my SQL, I just want to start creating more generic scripts, and depending on the case I decide to use the v$ view or dba_hist, or I decide to group by different columns to mine the AWR/ASH repository.


              You made me realize that all I needed to do to get what I wanted is to avoid rerunning the DEFINE statements. If I run the DEFINE statements just once, and after that I just use Ctrl+Enter to rerun the SQL, the Query Result tab is not pinned. I was always selecting the entire text (Ctrl+A) before executing, and there's no need to do that unless I decide to change one of my variables.



              • 4. Re: reuse query results tab when using DEFINE statements
                Gary Graham-Oracle

                I'm not constantly changing my SQL

                Ah, if that's the case then you should be aware of the Refresh (Ctrl+R) icon in the Query Result toolbar.  If no change to the DEF variable value or to the text of the SQL, then just refresh that tab.