2

Given the following table:

SQL> DESC MM02.MMRZET01;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LPT_ID                         NUMBER(19)
 COU_ISO_ID                     VARCHAR2(2 CHAR)
 PRV_ID                         VARCHAR2(3 CHAR)
 LPT_NM                         VARCHAR2(30 CHAR)
 LPT_TYPE_CD                        VARCHAR2(1 CHAR)
 TOW_ID                         VARCHAR2(5 CHAR)
 TWN_ID                         VARCHAR2(2 CHAR)
 TOW_PCODE_TOWN_ID                  VARCHAR2(5 CHAR)
 TIZ_ID                         VARCHAR2(2 CHAR)
 LPT_HAS_ALIAS_IN                   VARCHAR2(1 CHAR)
 LPT_HAS_PLACE_IN                   VARCHAR2(1 CHAR)
 LPT_ID_MASTER                      NUMBER(19)
 LPT_NMKEY_TX                       VARCHAR2(6 CHAR)
 LPT_NM_CRUNCH_TX                   VARCHAR2(30 CHAR)
 LPT_PRIORITY_NR                    NUMBER(5)

The following query fails miserably:

SQL> Insert into MM02.MMRZET01 (LPT_NMKEY_TX) values ('ÄÄÄÄ');
Insert into MM02.MMRZET01 (LPT_NMKEY_TX) values ('ÄÄÄÄ')
                                                 *
ERROR at line 1:
ORA-12899: value too large for column "MM02"."MMRZET01"."LPT_NMKEY_TX" (actual:
8, maximum: 6)

I've tried jumping through all possible hoops that could have been found on either google or here.

What am I doing wrong?

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
favoretti
  • 29,299
  • 4
  • 48
  • 61
  • This sounds like a problem with your client environment, not your database (though verify the DB character set is AL32UTF8). It looks like you're using SQL\*Plus, so what is `NLS_LANG` set to in your shell environment, and how is the shell configured? – Alex Poole Jul 19 '18 at 16:07
  • DB is set to AL32UTF8 indeed. I'm running SQL*Plus from a docker container. Potentially set to C, let me check that, thanks! – favoretti Jul 19 '18 at 16:08
  • @AlexPoole Although.. what should it be set to? :) – favoretti Jul 19 '18 at 16:10

1 Answers1

3

You'll see this if your $NLS_LANG can't handle multibyte characters:

$ export NLS_LANG="ENGLISH_UNITED KINGDOM.US7ASCII"
$ sqlplus usr/pwd@db

SQL> Insert into MMRZET01 (LPT_NMKEY_TX) values ('ÄÄÄÄ');

ERROR at line 1:
ORA-12899: value too large for column "MY_SCHEMA"."MMRZET01"."LPT_NMKEY_TX"
(actual: 8, maximum: 6)

With a multibyte setting it works:

$ export NLS_LANG="ENGLISH_UNITED KINGDOM.AL32UTF8"
$ sqlplus usr/pwd@db

SQL> Insert into MMRZET01 (LPT_NMKEY_TX) values ('ÄÄÄÄ');

1 row created.

My shell's $LANG is set to en_US.UTF-8 in this case, but changing that or my emulator (PuTTY) translation that doesn't seem to break it. I'm sure it can in some circumstances though. You really need the emulator, shell and DB session settings match to avoid oddities.

Incidentally, you can use a dummy query to see if something odd is happening; with NLS_LANG="ENGLISH_UNITED KINGDOM.US7ASCII":

SQL> select 'ÄÄÄÄ', dump('ÄÄÄÄ', 1016) from dual;

'????????'
------------------------
DUMP('????????',1016)
--------------------------------------------------------------------------------
????????
Typ=96 Len=24 CharacterSet=AL32UTF8: ef,bf,bd,ef,bf,bd,ef,bf,bd,ef,bf,bd,ef,bf,b
d,ef,bf,bd,ef,bf,bd,ef,bf,bd

so your insert is trying to put eight lots of character ef,bf,bd which is the replacement character and renders as a question mark here, into your 6-char column. The error you're getting is reasonable given what it actually trying to insert.

With NLS_LANG="ENGLISH_UNITED KINGDOM.AL32UTF8":

SQL> select 'ÄÄÄÄ', dump('ÄÄÄÄ', 1016) from dual;

'ÄÄÄÄ' DUMP('ÄÄÄÄ',1016)
---- -----------------------------------------------
ÄÄÄÄ   Typ=96 Len=4 CharacterSet=AL32UTF8: c4,c4,c4,c4

which is what you'd expect to see.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318