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:
sys.dbms_output.get_line(line => :line, status => :status);
Please rewrite the fetch of dbms_output using the following 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?
i want to say that problem is exists in version 10+