This discussion is archived
6 Replies Latest reply: Nov 8, 2012 8:56 AM by 973026 RSS

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

973026 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 ACE Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Jim. It could well be a resource issue.

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points