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