Oracle SQLPlus – Byte vs Char Length Semantics

oracle

The following code works fine in SQLDeveloper

declare
x varchar2(5 char);
begin
select 'üüüü' into x from dual;
end;
/

But when I run it in sqlplus I get

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

I get a similar error when I in sqldeveloper changes the variable declaration to 

x varchar2(5 BYTE)

The question is: why do I get the error in sqlplus and not in sqldeveloper?

Best Answer

Set NLS_LANG properly. Here is an example.

I use KiTTY for SSH client, with UTF-8 settings.

[oracle@o73 ~]$ file test.sql
test.sql: UTF-8 Unicode text

[oracle@o73 ~]$ cat test.sql
declare
  x varchar2(5 char);
begin
  select 'üüüü' into x from dual;
end;
/
[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 7 19:04:00 2018
Version 18.2.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL> @test
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4


SQL> Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0
[oracle@o73 ~]$ export NLS_LANG=.AL32UTF8
[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Mon May 7 19:04:13 2018
Version 18.2.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

SQL> @test

PL/SQL procedure successfully completed.

SQL Developer handles this automatically for you.

Related Question