Error: Character String Buffer Too Small

[asth01]

Hi,

I'm getting this error message when I run:

CREATE OR REPLACE FUNCTION MIG_USER.Display_Message (
OUTPUT_TEXT IN VARCHAR2
) RETURN NUMBER IS

--DECLARE
i NUMBER (10) DEFAULT 1;
original_text_length NUMBER (20) DEFAULT 0;

BEGIN
-- Break string into chunks of 255 characters and then display thru DBMS_OUTPUT.PUT_LINE
original_text_length := LENGTH (output_text);
DBMS_OUTPUT.PUT_LINE(original_text_length);

WHILE i <= original_text_length LOOP

DBMS_OUTPUT.PUT_LINE (SUBSTR (output_text, i, 255));
i := i + 255;

END LOOP;

RETURN i;

END;
/

What should I do to do away with the error?

Thanks

 

[SantaMufasa]

Asth,

I am not familiar with the wording of the error you assert in your subject line. Are you referring, instead, to this error:

&quot;ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes&quot;?

If so, you can deal with this problem by issuing the following command (either within your PL/SQL block or as an &quot;exec&quot; command before your PL/SQL block) prior to your first dbms_output.put_line invocation:

dbms_output.enable(1000000)

This above command increases the size of your dbms_output buffer from the 2000-character default to 1 million characters.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:44 (23Jan04) GMT, 09:44 (23Jan04) Mountain Time)

 

[asth01]

I'm getting these:

ORA06502: PL/SQL:numeric or value error: character string buffer too amall
ORA06512: at line 6

 

[dbtoo2001]

Is the length of the input message less than 255?

 

[SantaMufasa]

Asth,

I'm puzzled. I created your procedure, then successfully ran it with a NULL string (at the short end) and 10,000 characters (at the long end):

SQL> l
  1  CREATE OR REPLACE FUNCTION Display_Message (
  2  OUTPUT_TEXT IN VARCHAR2
  3  ) RETURN NUMBER IS
  4
  5  --DECLARE
  6  i NUMBER (10) DEFAULT 1;
  7  original_text_length NUMBER (20) DEFAULT 0;
  8
  9  BEGIN
 10  -- Break string into chunks of 255 characters and then display thru DBMS_OUTPUT.PUT_LINE
 11  original_text_length := LENGTH (output_text);
 12  DBMS_OUTPUT.PUT_LINE(original_text_length);
 13
 14  WHILE i <= original_text_length LOOP
 15
 16      DBMS_OUTPUT.PUT_LINE (SUBSTR (output_text, i, 255));
 17      i := i + 255;
 18
 19  END LOOP;
 20
 21  RETURN i;
 22
 23* END;
SQL> /

Function created.

*** NULL test ***
SQL> declare
  2  a number;
  3  begin
  4  a := display_message(null);
  5  end;
  6  /

PL/SQL procedure successfully completed.

*** Long test ***
SQL> declare
  2  a number;
  3  begin
  4  a := display_message(lpad('a',10000,'a'));
  5  end;
  6  /
10000
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaa
(... 37 additional sections that look like the 'a' output above)
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

PL/SQL procedure successfully completed.

I was, however, able to re-create your error message with the following code:

SQL> exec dbms_output.put_line(lpad('a',501,'a'))
BEGIN dbms_output.put_line(lpad('a',501,'a')); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at &quot;SYS.DBMS_OUTPUT&quot;, line 64
ORA-06512: at line 1

If I reduce the length of the output string (by 1) to 500, we see a different error (which we actually expect due to the 255-character maximum of a single dbms_output.put_line string.)

SQL> exec dbms_output.put_line(lpad('a',500,'a'))
BEGIN dbms_output.put_line(lpad('a',500,'a')); END;

*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at &quot;SYS.DBMS_OUTPUT&quot;, line 99
ORA-06512: at &quot;SYS.DBMS_OUTPUT&quot;, line 65
ORA-06512: at line 1

So, since I do not have your data to reproduce your actual environment, I must leave the follow-on troubleshooting to you (unless another Tek-Tip colleague can see something else from the tests/facts above).

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 18:14 (23Jan04) GMT, 11:14 (23Jan04) Mountain Time)

 

[asth01]

I tried passing:

'This is a very big string and it'll not fit into simple 255 limit of DBMS_OUTPUT.PUT_LINE. So I had to write a small procedutre to make it visiblein the OUTPUT window of DBMS_OUTPUT. Hope everyone will like it. It also doesn't eliminate any character so it's pretty raw in processing'

when it gives error.

But when I pass 'No Probs', it ran successfully ....

So there is something with the length. I even increased the buffer size to 1000000.

 

[BJCooperIT]

Try a long string without the embedded quote (').

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[asth01]

I tried with:

jshjshxsjxxxxjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888800000000000000000000000000000000000000000000000000000000000099999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999ttttttttttttttttttttttttttttttttttttttttttttttttttffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffsssssssssssssssssssssssssssssssssss2222222222222

http://wwwwwwwwwwwwwwwwwEND

And it failed again.

 

[SantaMufasa]

Asth and BJ,

I am STILL puzzled...I just successfully processed the string that failed for Asth:

SQL> declare
  2   a number;
  3  begin
  4   a := display_message('jshjshxsjxxxxjjjjjjjjjjjjjjjjjjjjj <...plus the rest of the 536 characters...> ') 
  5  end;
  6  /
536
jshjshxsjxxxxjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj88888
888888888888888888888888888888888888888888888888888888888888
888888888888888888888888888888888888888888888888888888888888
888888888888880000000000000000000000000000000000000000000000
000000000000009
999999999999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999ttttttttttttttttttttttt
tttttttttttttttttttttttttttfffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffffffffffsssssssssssssssssssssssssss
ssssssss2222222
222222[URL unfurl="true"]wwwwwwwwwwwwwwwwwEND[/URL]
SQL>

I've successfully run this on both Oracle 8.1.6 and 9.2.0.4 instances. Hmmmmmm....

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:44 (23Jan04) GMT, 13:44 (23Jan04) Mountain Time)

 

[asth01]

Is there anything that has to do with the parameter?

As I am not specifying the parameter length, can that cause error?


CREATE OR REPLACE FUNCTION Display_Message (
OUTPUT_TEXT IN VARCHAR2
) RETURN NUMBER IS

What length it'll assume?

 

[asth01]

It's Solved !

I was stupid and not looking at the SQL generated by TOAD.
It was defining the parameter to VARCHAR2 (22) which was not enough for it. So, when I changed it to 2000, it ran.

So I think If I 'm running the procedure from TOAD, the default length it takes for VARCHAR2 is 22.

Sorry for bothering you all....