Append varchar2 length in Oracle

Question

I have an oracle procedure which appends varchar2:

var1 varchar2(32767);

if nvl(length(var1),0) + length(var2||var3||var2||chr(10)||var4||chr(10)||chr(10)) < 32767 
then  var1:= var1||var2||var3||var2||chr(10)||var4||chr(10)||chr(10);
end if;

While I'm doing debug I can confirm that:

lengh(var1||var2||var3||var2||chr(10)||var4||chr(10)||chr(10))

is lower than 32767. But, when I try to execute line 3, it gives me an error:

"numeric or value error: character string buffer too small"

Can anyone help me here?

Przemyslaw Kruglej · Accepted Answer · 2013-11-19 11:33:12Z

2

What is your character set?

LENGTH function returns count of characters, not bytes. If you want to check the length of a string in bytes, use the LENGTHB function.

The problem here may be the fact that you store characters that take up more than 1 byte. The 32767 is the bytes limit for a VARCHAR2 (NVARCHAR2 too) string, not characters. So, after concatenation, your string may have less characters than 32767, but it may use more bytes to represent all of them, and you get the buffer too small error.

Collectives™ on Stack Overflow

Append varchar2 length in Oracle

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related