ORA-06502 when assign non english characters-CodePudding

I am facing a weird problem with oracle. I have a string which has some non English characters. I want to substring it to the length 10 and assign it to some variable. The substring function does it's job but the assignment fails with ORA-06502. It expects variable length to be 11.

Sample Code:

declare
  userName varchar2(10);
begin
  dbms_output.put_line('Length: ' || length(substr('JOHÑ NEWMAN',1,10)));
  userName := substr('JOHÑ NEWMAN',1,10);
end;

Output:

Length: 10

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

CodePudding user response：

The Oracle SUBSTR method is not multibyte character compatible.

You have two options:

Use SUBSTRB, which is multibyte character compatible.
Change the NLS_LENGTH_SEMANTICS setting to CHAR (default is BYTE)

See this post from dba-oracle.com for reference.

CodePudding user response：

By default (unless you've set nls_length_semantics to char), varchar2(10) allocates 10 bytes of storage. Depending on your database character set and your data, that may be sufficient for 10 characters or it may be sufficient for many fewer characters.

You can declare your variable as varchar2(10 char) to allocate space for 10 characters regardless of the length in bytes. And you can use lengthb to see the length of a string in bytes. This should show that your string is 10 characters and 11 bytes in length.

declare
  userName varchar2(10 char);
begin
  dbms_output.put_line('Length: ' || length(substr('JOHÑ NEWMAN',1,10)));
  dbms_output.put_line('LengthB: ' || lengthb(substr('JOHÑ NEWMAN',1,10)));
  userName := substr('JOHÑ NEWMAN',1,10);
end;
/