Toad World® Forums

Dbms_output is able to show only 4000 characters


#1

Hi!
Ora 10 dbms_output could show 32h characters. Sqlnav shows only 4000.
Try following:
declare
result varchar2(32000);
begin
for i in 1…4000 loop
result:=‘x’||result;
end loop;

dbms_output.put_line(result);
end;
Output is showed.
Then change 4000 to higer value for example 4001.
Output is not showed.
In error.log we can see:
SQL NAVIGATOR ERROR LOG

v6.0.0 2006-11-10 12:25:03 Exception EOleException in module SQLNav6.exe at 00020E66. Task “SQL Editor Execution”: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at “SYS.DBMS_OUTPUT”, line 148
ORA-06512: at line 2.

The same code in sqlplus shows output.

Second thing is that output window is too small to show all results…

Regards Piter


#2

Hi Piter,

We will look into this for you and will get back to you early next week.

thanks and regards,
Bruce


#3

Hi Piter,

Just let u know that we are still working on this without any success yet. We could not reproduce your issue exactly, ie. no exception, but we could see the spool and output windows do not display any result with the loop of 4001. We will try to get back to you later with updates on the progress :-).

Thanks,
Bruce


#4

Hi Because there is no exception , output is not showed, error is only in error.log in sqlnav profile.

Regards


#5

Hello,
any news on this one? SQLNAV 6.0.0.1055 and 6.1.0.1071 show

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at “SYS.DBMS_OUTPUT”, line 148
ORA-06512: at line 1

when something like this

declare
x varchar2(20000);
begin
for a in 1…5000 loop
x := x||‘s’;
end loop;
dbms_output.put_line(length(x));
dbms_output.put_line(x);
dbms_output.put_line(‘finish’);
end;
/

5.5.4.847 doesn’t even show an error message

Andres


#6

I tried to recreate the bug but I got this error instead: “line length overflow, limit of 255 chars per line”. It’s the same for 5.5. Did I miss something?

Gwen


#7

Hi Gwen,
the restriction of 255 bytes per line was there until 10.2 raised it to 32767 bytes. So maybe you tried it in a pre-10gR2 database?

Andres


#8

Hello,

I tried Adres’s script on SQL Nav 6.1, using a 9.2.0.7 client

  1. against a 9.2.0.7 database:
    ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
    (as soon as one tries to output more than 255 characters)

  2. against a 10.2.0.4 database:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    (as soon as one tries to output more than 4000 characters)

With SQL*Plus (9.2.0.7), one gets the same error against a 9.2.0.7 database, but against a 10.2.0.4 database, I get:
ORA-06502: PL/SQL: numeric or value error: host bind array too small
(as soon as one tries to output more than 255 characters)

It would seem that the application retrieving the dbms_output output can impose a limit on how many characters it allows (SQL*Plus: 255, SQL Nav: 4000). Or maybe it’s a built-in limitation of the 9.2.0.7 client?

BTW: I cannot test with a 10g(R2) client yet.


#9

Thanks Andre. I tried 10G and 11G DB this time and strange enough it didn’t give me any error. In the output window, I only got the length of the string and that’s it. The string wasn’t printed out and there wasn’t any error. I tried in 5.5 as well and got the same result. Any more suggestion?

Gwen


#10

Here’s what I get in SQL Nav 6.1 vs. a 10.2.0.4 database…


#11

I see the difference now. I was executing a func instead of an anonymous block. I will try to find the old CR or log a new one in. Sorry guys for the long delay.

Gwen