I am using Toad for Oracle version 13.0.0.80, and would like to know how to have DBMS_OUTPUT on by default.
I noticed that when we moved off version 12, I have to remember to enable DBMS_OUTPUT every time I load TOAD if I want to use it. In the previous versions there was a way to have it enabled when TOAD loads, but I cannot find a way to do this in version 13.
I have done a search in the TOAD forums as well as Google, but haven’t come up with anything.
Any update on this? I'm on 13.1.0.78 and still haven't found a solution. As noted by garihood_062, this worked fine in v12 and but the setting doesn't persist in 13.
I have logged this to look at soon. There's some funk going on there with the controls where nothing is enabled by default and whatever you choose is not remembered on the next Toad session.
My response was a bit premature. What I see as an issue is a less than desirable default value on first run. Once I choose one of the two polling options (automatic or interval) those options persist when I close Toad and restart. The button that enables output to the far left does not persist its state in 13.1 nor does it in 12.0 for me.
Two buttons in green remember their state, button in red does not. Can you explain exactly what you were doing in Toad 12 that is not working in 13.1?
The 2 polling options retain their setting across sessions, but the enable output option does not. It always resets to "off" (red).
This was actually the same behavior in 12, but dbms output displayed even when it was red (off). Perhaps that was a bug in 12, but the end result is that it did what I wanted - allowed the dbms output to display without having to click the icon every session. Perhaps the visual indicator in 12 was simply incorrect.
The ideal solution is to allow this toggle to retain the setting across sessions, or have a configuration option to set the default toggle value.
I agree with Jeff. The ideal solution would be for the button’s state to persist between sessions, and for its visual cue to be consistent with its behavior.
Before I just start making changes willy nilly I think I'm missing something here. The fact that 3 simple toolbuttons can be so confusing to me says a lot as well! I believe there is room for improvement.
My earlier screenshot highlights the two buttons for the polling method in green. The left button is for Automatic and the right for Interval. They function as...
Interval polling
Does nothing if DBMS Output is in the disabled (red) state.
When DBMS Output is enabled, polling occurs every N seconds.
Restarting Toad remembers this setting, but DBMS Output returns to the disabled state.
Automatic polling
The enabled state of DBMS Output has no effect on Automatic polling. When you execute your own code DBMS Output is enabled, your code is executed, and DBMS Output is fetched.
Restarting Toad remembers this setting and DBMS Output is fetched upon execution of your code regardless of the DBMS Output enabled state.
I'm seeing this same behavior in 12.0 and 13.1.
If you use Automatic polling does that do what you're looking for? That's the only thing I can come up with and it causes DBMS Output to be fetched following a restart of Toad, despite the indicator button being in the disabled state.
I was testing Toad 12.0. If there is another version of Toad 12 that I should be looking at or a particular set of steps to follow please let me know.
Nuts. I had a big long response with all sorts of screen prints, but I'm being prevented from including more than one image. Oh well. Here goes without the images.
I agree. Something is goofy here. I ran another set of tests:
Example 1 - Nothing enabled:
As expected no output.
Example 2 - Now, let's click that automatic polling:
As expected, no output.
HOWEVER, if I press F5 AGAIN, the output will show up!
Example 3 - DBMS Output turned on (green), no automatic polling:
As expected, output shows up, even without the automatic polling turned on.
Example 4 - enable both DBMS Output and Automatic Polling,
This works as expected. Output shows up after execution.
However, if I quit Toad and restart, the DBMS Output is off (red).
If I enable automatic polling, then exit Toad and restart, automatic polling is enabled, but no output is gathered. Running scripts after restarting follows example 2 above.
It looks like there are some inconsistencies here. Running a script two times in a row will display the second output as expected but not the first. This is independent of the DBMS Output setting.
I still think the best solution is to just allow the DBMS Output setting to persist across sessions, but now I'm a bit concerned about the strange behavior exhibited in Example 2.
F5! Ah. I always execute single statements using F9 since F5 is largely a SQL*Plus emulator and does not follow the complete execution logic of the Editor as used by F9. F5 will still run your code, but lacks some additional features the Editor supports for non-script execution. F5 here is the trigger it seems.
These would produce output in Toad 12.0, but they do not in 13.1. There are multiple conditions the script engine checks to decide if output should be captured and the toolbuttons on the DBMS Output panel are just part of the story. Automatic polling should always capture output regardless of the Red/Green output enabled button. This is confusing to say the least.
This is the bug.
Yes, I see this too. One of the conditions script engine uses to enable/disable output collection is whether or not it's being executed in an Editor context. It does not set its Editor context variable until after it decides if output is needed. Your first script runs, fails to enable output as expected, but does set the Editor context flag. Executions 2-N now have the Editor context flag set appropriately and it works.
I've fixed all of this so that executing as script (F5) will produce and collect output just about every time you execute. You really have to go out of your way to completely disable DBMS Output for F5 execution. If you are also a beta user, or wish to become one, our next public beta (13.2.0.111 or later) will have this fix in place. It should be released sometime later in the day on Thursday.
I will look at those toolbuttons now and try to better achieve the same in an more intuitive manner.
It seems like a simplification is in order here. The existing three setting for a single action (i.e. "show me output") seem logical when viewed independently, but when their combinations produce confusion, then I think it's time to review them.
I think that the green/red light should become a "master switch". If it's on, dbms_output will always be displayed. If it's off, it will never be displayed. Currently, the latter isn't the case. As Jeff pointed out, everything can be off, yet running a script a second time will produce output.
Currently, selecting the right polling option (Interval polling) flips the light green. That's fine. But why doesn't the left polling option (poll after execution) do the same? I suggest that we make them both do the same thing - either they both flip the light green, or they both don't.
If you can make the above happen, and then make the status of all three settings persist across sessions (or make them Options settings), then everything would make crystal clear sense.
Not sure if this will add anything to the F5/F9 conversation, but I always use the "Execute as script" button. Does that button trigger the F5 or F9 action? I assume F9.
Execute script is F5. F5 is a completely different beast and technically falls under the umbrella of our debugger logic in many regard. There are options for "debuggers" that affect script execution.
It seems like a simplification is in order here.
No doubt!
I think that the green/red light should become a "master switch"
This does not work because of the Automatic polling method. Automatic polling is designed enable output collection, execute your code, collect output, and disable output collection all in one fell swoop. It's really unrelated to the far left master switch we have now. This button is the source of the problem IMO and I take full credit for it! I have no idea what I was thinking (or not thinking) then.
A dropdown there could replace all of those toolbuttons. It could have a few values for Output collection and polling method...
Disabled
Output is disabled completely
Automatic
Output is intelligently enabled at point of execution; this could be renamed to something more fitting
Interval
Output is enabled, all output produced for the session is gathered regardless of where it came from
Those 3 options reflect the current permutations with one difference. It's currently possible to leave your polling method set to interval and then quickly toggle output collection on/off with one click. A dropdown would require 2 clicks to do the same.
You're welcome. In the short term the immediate issue at hand is fixed. In the long term we're discussing some changes here to simplify DBMS output. Once something solid is ready I'll add an item to the Idea Pond and link it back here. We've found a lot of funk in how output is gathered and where it's displayed in particular when Toad's threaded queries option is enabled, when output is produced from a debugger session vs. a normal, etc.
The issue in this post is for a simple bug, but it resulted in an evaluation of the overly complex and confusing implementation of DBMS Output in Toad. I've created a post in the Idea Pond with some ideas for a master toggle and a simplified viewing of output. Please have a look, comment if you'd like, and cast your vote for it if you agree. Thanks.
I believe there is room for improvement.