Lines written using dbms_output takes a very long time to fetch

I have database method that uses dbms_output.put_line to output debug data.

The method itself executes in seconds, but the additional time it takes to fetch 1000+ lines of output is very long.

This is because PLD calls the following code 1000+ times:

Code

sys.dbms_output.get_line(line => :line, status => :status);

Please rewrite the fetch of dbms_output using the following code:

Code

sys.dbms_output.get_lines (lines => :lines, numlines => :numlines);

where :lines is an array of type dbms_output.chararr and :numlines is a variable that indicates how many lines are fetched. The maximum of lines fetched in one call is the max size of an pls_integer (more than 2 billion) so a couple of loops until there is no more lines to fetch should do. If the way PLD calls the Oracle database can not handle index-by arrays, the array can be turned into a clob before returning and then be splitted into lines after return.

In all cases returning thousands of lines can be done in a matter of seconds using the latter 'bulk' method instead of minutes fetching the lines one by one.

The exact format for the call may be dependant of the database version, but I imagine this is a bump that can also be solved.

Will such an improvement be implemented in version 10 of PLD?

This is indeed on the list of enhancement requests.

in last version 10.0.3.1701 that behavior is alive. when it can be solved?

What do you mean by "alive"?

i want to say that problem is exists in version 10+

I observe dramatic performance boost upon retrieving DBMS_OUTPUT lines in PLSDev v.10.
It still takes time, but I would say it is ten times faster now.

Marco, when will this issue be addressed by the development team?
The rewrite using the method sys.dbms_output.get_lines (the plural version, my emphasis) should not be that complicated?
Retrieving dbms_output on a slow VPN connection takes forever ...

Hope soon to hear some news about this issue.

@Marco: do you have an planned code change for this issue?