ORA-06502 [message #682256] |
Tue, 13 October 2020 14:48 |
|
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 |
Solomon Yakobson
Messages: 3275 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 |
|
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 |
Solomon Yakobson
Messages: 3275 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.
|
|
|