6 Replies Latest reply: Nov 8, 2012 10:56 AM by 973026 RSS

    how to suppress output from a SELECT in the Script Output page

    973026
      Hi there,

      I'm running a particular piece of SQL with some bind variables. The aim is to get the execution
      plan using DBMX_XPLAN.DISPLAY_CURSOR.

      To get the variables and bind variable values in them to work, I've found that I have to select
      the statements in the Worksheet, and then hit F5.

      That all works fine, but the issue i have is that this particular SQL returns over 60,000 rows.

      I don't want to see the rows, and furthermore there are limits on how many rows can be displayed
      in SQL developer.

      Is there a way I can suppress the results of the SQL query appearing in the Script Output window?

      I've tried :

      set termout off
      set autotrace traceonly
      set pagesize 0

      But nothing makes any difference. Surely I can stop it from behaving in this way? Any suggestions?

      I am using SQL Developer 3.2.09

      thanks!

      Paul Stuart
        • 1. Re: how to suppress output from a SELECT in the Script Output page
          Gary Graham-Oracle
          Hi Paul,

          I don't believe so. Even spooling the output for a query (whether inline or embedded via @<scriptname>.sql) does not suppress output to the worksheet result pane. And internally, for Explain Plan and Auto-Trace, SQL Developer uses dbms_xplan.display rather than display_cursor.

          Probably your best bet is to reduce the total output via Tools | Preferences | Database | Worksheet | Max Rows to print in a script. That setting does not affect the SQL sent to the database, it just limits how much of the result set SQL Developer bothers to display when you Run Script. Any information you obtain from display_cursor should be unaffected. Of course, the worksheet sends lots of other SQL to the database you do not see, so relying on the default "look at the last cursor" behavior will not work. I assume you have already accounted for that.

          Regards,
          Gary
          SQL Developer Team
          • 2. Re: how to suppress output from a SELECT in the Script Output page
            Jeff Smith Sqldev Pm-Oracle
            Also I'd like to know more about why you can't get the binds to work properly if you execute the query via F9/ctrl-enter.
            • 3. Re: how to suppress output from a SELECT in the Script Output page
              Gary Graham-Oracle
              Not sure. Possibly Paul is referring to the fact that given the following anonymous block
              variable id  number
              exec :id := 7369
              select * from scott.emp where empno = :id
              Run Script accepts the value passed in the bind variable id, but Run Statement ignores it and offers the Enter Binds dialog instead. Or perhaps he has encountered some problem with a more complex SQL case, something like reported in the following post:
              Re: Bug EA 4 (3.0.03.97) Bind Variable Not Declared on Insert from select

              Not sure if any additional work has occurred in this area since changes for 3.1 noted in
              Bug 12385310 - MAKE POPUP BIND SUPPORT IN THE WORKSHEET MORE GENERIC
              • 4. Re: how to suppress output from a SELECT in the Script Output page
                973026
                Thanks Gary and Jeff for your replies.

                And you're right - a rather obvious way around this is to set the Max Rows parameter - I hadn't thought
                of that.

                I tried setting the Max Rows to 2000, and now the commands work.

                Although, as an aside, I can't see why SQL Developer should have any issues returning 67,000 rows from a
                select? What i observed is that several thousand rows were returned to the Script Output window, and then
                the command appeared to hang (the sql itself had definitely completed inside the database).

                I'm using the 64 bit SQL Developer, with the 64 bit 11gR3 client. Surely returned 67,000 rows to the screen
                should be a snap? I would be inclined to consider that a bug.

                By the way, Gary you are exactly correct about how I was running the bind variables. By using F5, SQL developer
                considers the selected commands to be script, and doesn't prompt you for the bind variable values. A nice feature
                by the way - saves having to put commands in to seperate files and executing them.

                regards

                Paul Stuart
                • 5. Re: how to suppress output from a SELECT in the Script Output page
                  Jim Smith
                  It is possible that SQL Developer is running out of memory displaying 67000 rows. Unike SQL*PLus where rows are just written to screen and forgotten, the SQL Developer script output window is a java window and will effectively store the display form of all the rows to allow for scrolling and refreshing the window.

                  To adjust the amount of memory allocated to SQL Developer you can modify the
                  AddVMOption  -Xmx640M
                  line in <sqldev>\ide\bin\ide.conf
                  • 6. Re: how to suppress output from a SELECT in the Script Output page
                    973026
                    Thanks Jim. It could well be a resource issue.

                    I've changed the config file so hopefully should perform better in future.