Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502 (Oracle 12 C windows)
ORA-06502 [message #682256] Tue, 13 October 2020 14:48 Go to next message
anncao
Messages: 87
Registered: August 2013
Member
I got an error ORA-06502: PL/SQL: numeric or value error: character string buffer too small
for a sql statement running in SQLPLUS 12.2..
It works OK in 12.1 before. and it also currently runs fine in sql developer.

The field is like below,
rtrim(replace(replace(replace(replace(dbms_lob.substr(ce.fulldescription,3800),chr(10),''),chr(13),' '),'"',''),'/','/')) fulldescription, --Max 3800

and it is in with clause, we changed to the field from with clause to select clause, then it works.
Why is that?

Thanks
Re: ORA-06502 [message #682258 is a reply to message #682256] Tue, 13 October 2020 17:29 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Column ce.fulldescription is CLOB, right? Therefore 3800 in dbms_lob.substr(ce.fulldescription,3800) means 3800 characters so based on the error I can assume some of these 3800 characters are multibyte characters and length in bytes exceeds 4000 which is VARCHAR2 limit for non-extended VARCHAR2 datatype. Anyway, reduce number of characters in substring. Check max bytes per character for your database character set and use trunc(4000 / max_bytes_per_character) in dbms_lob.substr to guarantee it will never exceed 4000 bytes.

SY.
Re: ORA-06502 [message #682259 is a reply to message #682258] Tue, 13 October 2020 18:00 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Thanks, how to check max bytes per character, I see in our database property: NLS_CHARACTERSET:AL32UTF8; NLS_NCHAR_CHARACTERSET=AL16UTF16.
Yes, the description is a clob field
The strange thing is if we run the same query in SQL developer, there is no error, but if it is run in sqlplus it shows the error.
Also if we move it from with clause to the select part, then it works.

[Updated on: Tue, 13 October 2020 18:01]

Report message to a moderator

Re: ORA-06502 [message #682260 is a reply to message #682259] Tue, 13 October 2020 18:51 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
The AL32UTF8 character set implements the UTF-8 encoding form and supports the latest version of the Unicode standard. It encodes characters in one, two, three, or four bytes. So use dbms_lob.substr(ce.fulldescription,1000)

SY.
Previous Topic: converting Unix Time format to readable format
Next Topic: Pivot using SQL in Oracle
Goto Forum:
  


Current Time: Thu Mar 28 04:54:32 CDT 2024