0

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?

  • SQLPlus for some reason is reporting it takes 6 bytes to store that character and SQL Developer is reporting only 2. It is a multi-byte character in the Unicode spec...SQLPlus isn't unicode compliant out of the box so not sure where it's going squirrely. But run this and you'll see why SQL*Plus isn't happy SQL> select lengthb('ü') from dual; LENGTHB('??') ------------- 6 – thatjeffsmith May 7 '18 at 12:25
  • so your character is considered a unicode supplementary character and docs show that ' The supplementary characters are treated as two separate, user-defined characters that occupy 6 bytes' docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm and sqldev has full unicode support and is able to avoid this, somehow, my guess – thatjeffsmith May 7 '18 at 12:40
  • What is the value of NLS_LENGTH_SEMANTICS? – ArtBajji May 7 '18 at 14:50
1

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.

0

Please run this command in SQL*Plus and SQL Developer:

select * from nls_session_parameters;

and compare the output.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.