Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    ORA-12899: value too large for column; NLS_LENGTH_SEMANTICS

    • Article Type: General
    • Product: Aleph
    • Product Version: 18.01

    Description:
    When bib records are added/updated in abc01 in the GUI, ue_01 creates the correct z01, z02, z97, z98, etc. index updates, but, when p_manage_02 (or p_manage_01) is run, the result is zero z01/z02 (or z97/z98/z95) records.

    I see the following errors in the abc01 $data_scratch:

    In abc01/scratch/z01_dmp_ora.log.nn:

    Record 1: Rejected - Error on table ABC01.Z01, column Z01_REC_KEY.
    ORA-12899: value too large for column "ABC01"."Z01"."Z01_REC_KEY" (actual: 240, maximum: 80)

    and the same for z02:

    In abc01/scratch/z02_dmp_ora.log.nn:

    Record 1: Rejected - Error on table ABC01.Z02, column Z02_REC_KEY.
    ORA-12899: value too large for column Z02_REC_KEY (actual: 27, maximum: 18)

    <etc.>


    (Note: the $data_scratch p_manage_02_a_1.log and p_manage_02_a_2.log files don’t indicate any error, nor does the main log in $alephe_scratch. )

    I submitted p_manage_02 on the usm01 data; I see the same problem there.

    I ran p_manage_07 for abc30. That does not have this problem; it ran successfully.

    Then I ran p_manage_01 for abc30. It *does* have the problem.

    What p_manage_01 and p_manage_02 have in common is that they both use "FIX load_mode = DIRECT". I believe that our Oracle 11 is somehow not working correctly in the case of this DIRECT.

    Resolution:
    The following oddities have been noted in your v20:

    SQL> desc z02
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    Z02_REC_KEY NOT NULL CHAR(18 CHAR)
    Z02_DOC_NUMBER NOT NULL CHAR(9 CHAR)


    This *should* show "CHAR(18)" not "CHAR(18 CHAR)". And

    SQL> select COLUMN_NAME name , data_type type, data_length from
    2 user_tab_columns where TABLE_NAME='Z02';

    NAME TYPE DATA_LENGTH
    ------------------------------ ---------- -----------
    Z02_REC_KEY CHAR 54
    Z02_DOC_NUMBER CHAR 27

    Which is *not* what is defined…

    We believe that this is caused by this setting (which can be seen in util o/6/1):

    NLS_LENGTH_SEMANTICS CHAR

    It *should* be:

    NLS_LENGTH_SEMANTICS BYTE

    The problem can be corrected by the following command:

    SQL> alter system set nls_length_semantics=byte;

    [Later:] After doing the above "alter" command and restarting Oracle, the p_manage_01, p_manage_02, etc., jobs worked correctly.


    • Article last edited: 10/8/2013