This content has been marked as final. Show 6 replies
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.
SQL Developer Team
Not sure. Possibly Paul is referring to the fact that given the following anonymous block
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:
variable id number exec :id := 7369 select * from scott.emp where empno = :id
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
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
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.
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
line in <sqldev>\ide\bin\ide.conf