When I run a test script that uses HTP to produce output, the script finishes quickly but then PL/SQL developer hangs for as much as 10 minutes while it retrieves the HTP output.
I peeked at the network traffic and it looks like PL/SQL Developer retrieves HTP output one line at a time using something like:
Since htp considers a "line" to be 256 bytes (or less) please consider speeding this up by getting multiple "lines" at a time. I use code similar to the following:
Note that you can get even better performance by binding something larger than a 32k string to the block for output (like a clob or an array of strings). The less often the declare..end block is executed, the better.
I peeked at the network traffic and it looks like PL/SQL Developer retrieves HTP output one line at a time using something like:
Code:
begin
htp.flush;
:line := htp.get_line(:rows);
end;Since htp considers a "line" to be 256 bytes (or less) please consider speeding this up by getting multiple "lines" at a time. I use code similar to the following:
Code:
-- get_line returns up-to-256-byte "lines". Call that up to
-- 126 times to get up to (126*256=) 32256 bytes per round-trip.
declare
local_rows number := 0;
end
htp.flush;
while ( :rows > 0 and local_rows < 127 ) loop
:line := :line || htp.get_line(:rows);
local_rows := local_rows + 1;
end loop;
end;Note that you can get even better performance by binding something larger than a 32k string to the block for output (like a clob or an array of strings). The less often the declare..end block is executed, the better.
