OraFAQ Forum: SQL & PL/SQL » ora-06502 when printing long varchar

Home » SQL & PL/SQL » SQL & PL/SQL » ora-06502 when printing long varchar

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ora-06502 when printing long varchar [message #7958]

Fri, 18 July 2003 12:23

soy
Messages: 6
Registered: July 2003

Junior Member

In my declare section I have a variable that is:
varx varchar2(2000) := 'a really long string';
The string is actually around 1800 characters long
Inside the begin block I do:
dbms_output.enable(10000);
dbms_output.put_line(varx);

When I run the plsql code I get:
ORA-06502: PL/SQL: numeric or value error

PL/SQL procedure successfully completed.

If I take the put_line out then all is well and no error. What is going on? How do I fix this?

Report message to a moderator

Re: ora-06502 when printing long varchar [message #7961 is a reply to message #7958]

Fri, 18 July 2003 12:57

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Soy,

The DBMS_OUTPUT.PUT_LINE procedure has an unfortunate upper limit of 255 characters:

SQL> DECLARE
  2      l_ok_string         VARCHAR2(1000) := RPAD('X',25<b><font color=blue>5</font></b>,'X');
  3      l_bad_string        VARCHAR2(1000) := RPAD('X',25<b><font color=red>6</font></b>,'X');
  4  BEGIN
  5      DBMS_OUTPUT.PUT_LINE('1');
  6      DBMS_OUTPUT.PUT_LINE(l_ok_string);
  7      DBMS_OUTPUT.PUT_LINE('2');
  8      DBMS_OUTPUT.PUT_LINE(l_bad_string);
  9      DBMS_OUTPUT.PUT_LINE('3');
 10  END;
 11  /
1
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX....
2
DECLARE
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 84
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at line 8
  
  
SQL>

See this link for some potential workarounds.

But you're getting a different error: Are you sure you're not putting more than 2000 characters into your varx variable?

SQL> DECLARE
  2      x   VARCHAR2(5);
  3  BEGIN
  4      x := '123456';
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
  
  
SQL>

Good luck,

Art

Report message to a moderator

Re: ora-06502 when printing long varchar [message #7962 is a reply to message #7961]

Fri, 18 July 2003 14:01

soy
Messages: 6
Registered: July 2003

Junior Member

I'm getting a slightly different error now but I think it is the same problem.
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 99
ORA-06512: at "SYS.DBMS_OUTPUT", line 65
ORA-06512: at line 78

here's my code:
http://www.havennet.net/temp/failrun.txt

So doing what was in the previous link you posted should get around the 255 limit (using the my_dbms package)?

Report message to a moderator