dbms_output and 10g

[ScottMattes]

I am having to customize a new install of PLSD and since we are starting to use Oracle 10g DB I was wondering what does PLSD do with DBMS_OUTPUT lines > 255 long and more than the old limit (1,000,000) of total buffer size?

It looks like I have to decide how much to limit total size to (or, if I put in 0 does PLSD translate that to 'unlimited').

 

[Marco Kalter]

PL/SQL Developer currently only supports dbms_output up to 255 characters per line, and there is no support for an "unlimited" buffer size yet. This is on the to-do list though.

 

[CTzen]

Marco,

actually, I think you already support both features

Here is the script

cl scr
set linesize 300
set echo on

set serveroutput on size 0
begin
    dbms_output.put_line('Hello, World!');
end;
/

set serveroutput on size 1000
begin
    dbms_output.put_line('Hello, World!');
end;
/

set serveroutput on size 1000
begin
    dbms_output.put_line(rpad('x',299,'x'));
end;
/

spool c:\delme.txt
set serveroutput on
begin
    for i in 1..1111
    loop
        dbms_output.enable(buffer_size => 2000000);
        dbms_output.put_line(rpad('x',2999,'x'));
    end loop;
end;
/
spool off

 

[CTzen]

Couple more observations

1. One cannot set buffer size bigger then 1mb via set serveroutput on size 2000000 and have to use dbms_output.enable(NULL); instead.

2. While printing results back, PLD only prints first 1000 characters of each line.

Here is another script to play with

set serveroutput on size
set linesize 2000

spool c:\delme.txt
declare
    l_line  varchar2(10000);
    l_bytes number;
begin
    dbms_output.enable(buffer_size => null);

    l_bytes := 0;
    for i in 1..1111
    loop
        l_line := 'line: '||ltrim(to_char(i, '00000'))||rpad(' ', 989, 'x');
        l_bytes := l_bytes + length(l_line);
        dbms_output.put_line(l_line);
    end loop;

    dbms_output.put_line('Bytes printed: '||l_bytes);
end;
/
spool off

Output

SQL>
Started spooling to c:\delme.txt

line: 00001 xxxxxxxxxxxxxxxxxxxxxxx ...
...
line: 01111 xxxxxxxxxxxxxxxxxxxxxxx ...
Bytes printed: 1111000

PL/SQL procedure successfully completed

Executed in 0.187 seconds
Stopped spooling to c:\delme.txt