EXEC dbms_output.put_line( LPAD( 'X', 32767 ) );
When polling for output afterwards (manually or automatically), I get the following exception (Oracle 12.1 and 19c):
ORA-06502: PL/SQL: numeric or value error: host bind array too small
ORA-06512: at line 2
Can you reproduce this?
Change your options as such:
Bump down "Lines per fetch" to 10 or so if you have any problems with it using too much memory.
I switched from our prior method of retrieving dbms_output (which used lobs) due to intermittant problems that some were having.
That works, thank you!
Is the default for a new TOAD installation the maximum size?
What would be a reason to configure a lower size?
The default line size is 1000.
The downside of this technique is that it wants to use memory = (Line size * Lines per fetch).
Higher "lines per fetch" means lots of lines are retrieved more quickly
Higher "max line size", well, you know what that is for.
Both of them high means it takes a lot of memory....which is probably fine for 64 bit installations, most of the time.
So it's a bit of a balancing act.
I'll change it if 1000 proves to be unpopular.
I tried this now with various settings (64 bit).
BEGIN
FOR i IN 1 .. 100000 LOOP
dbms_output.put_line( RPAD( 'X', 1000, 'X' ) );
END LOOP;
END;
Peak memory usage seems to be almost identical regardless of the two settings, according to taskmanager. With lower Lines per fetch it just takes longer to reach that peak.
Does this look different for you?
10sec, ~1.8GB: 
14sec, ~1.8GB: 
18sec, ~1.8GB: 
If you keep the current default, could you display a custom error message pointing to the settings instead of the generic Oracle exception?
Would also be nice to be able to cancel that fetch 
Mine is similar, except you must have a much faster network and/or database than I do because my timings were a lot slower.
And running that in 15.1 - forget it. I gave up and killed the process.
Finally I changed the loop to 1 .. 10000, removing one of the 0's.
16.0 is faster (took about half the time with settings at 1000/1000 as Toad 15.1), and memory usage is similar.
The memory usage you're seeing in task manager is mostly for putting all of that data in the output pane.
The memory usage I was referring to can be better described as "what Toad has to set aside just to do the pl/sql call to fetch the dbms_output in the first place". So, even if you are about to fetch only 1 char of dbms_output, Toad will still have to reserve room, temporarily, for 32767*1000 (in the first example) chars of data. That's really what I was referring to. It's only going to be a problem in cases where memory is almost exhausted.
Good idea about the custom error message though. I'll add that to my to-do list.
Hi @JohnDorlon , we're also currently hitting this problem. Thanks for the explanations and the hints to the config.
But, I also have noticed, that when the limit is hit and we run into the error, the output pane will not display any further outputs (for subsequent executions) although we changed the options or reduced the linesize of our outputs. The editor has to be closed and reopened in order to get the output pane (within the limits) working again.
Good morning TOAD team,
I ran into this issue long ago when I used to optimize SQL for other teams. I don't know if the following couple lines could be the TOAD defaults, maybe another option section, but they resolved all issues I was having with F5 runs running of RAM: I addition the script output became much more readable, too.
set linesize 4096;
SET PAGESIZE 50000;
Larry
I just made a change for the next beta so that the error message tells you what to change in options to fix it. I just tested it and I did not observe the problem you note. I believe my change fixed that too.
@wickliffe.larry
A bigger line size would be a nice default. I hate the wrapping around of output. We try to make it look like SQL*Plus though, so if we automatically add that, then user runs script in SQL*Plus, the output will be different.
-John