After upgrading from 11g to 12.2.0.1 Oracle, application folks claim the sqlldr of certain table(s) fail now with ORA-01830
LOG FILE)
pg MARA.log
SQL*Loader: Release 12.2.0.1.0 - Production on Tue May 26 14:48:06 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Control File: MARA.ctl
Character Set UTF8 specified for all input.
Using character length semantics.
Data File: /home/pe/dwusert/AIDWMFG/data/ZEW9B997_new.dat
Bad File: /home/pe/dwusert/AIDWMFG/data/MARA.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Silent options: FEEDBACK
Table STG_MFG_AI_MATERIAL_GENERAL, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
MATERIAL_NUMBER FIRST 54 CHARACTER
CREATE_DATE NEXT 24 DATE YYYYMMDD
SQL string for column : "decode(:create_date,'00000000',NULL,:create_date)"
CREATED_BY NEXT 36 CHARACTER
MODIFY_DATE NEXT 24 DATE YYYYMMDD
SQL string for column : "decode(:modify_date,'00000000',NULL,:modify_date)"
MODIFIED_BY NEXT 36 CHARACTER
DELETION_FLAG NEXT 3 CHARACTER
MATERIAL_TYPE NEXT 12 CHARACTER
MATERIAL_GROUP NEXT 27 CHARACTER
OLD_MATERIAL_NUMBER NEXT 54 CHARACTER
BASE_UNIT_OF_MEASURE NEXT 9 CHARACTER
ORDER_UNIT NEXT 9 CHARACTER
BASIC_MATERIAL NEXT 42 CHARACTER
ISO_DESC NEXT 54 CHARACTER
LAB_DESIGN_OFFICE NEXT 9 CHARACTER
PURCHASING_VALUE_KEY NEXT 12 CHARACTER
GROSS_WEIGHT NEXT 42 CHARACTER
SQL string for column : "TO_NUMBER(:gross_weight,'9999999990.000S')"
NET_WEIGHT NEXT 42 CHARACTER
SQL string for column : "TO_NUMBER(:net_weight,'9999999990.000S')"
UNIT_OF_WEIGHT NEXT 9 CHARACTER
VOLUME NEXT 42 CHARACTER
SQL string for column : "TO_NUMBER(:volume,'9999999990.000S')"
VOLUME_UNIT NEXT 9 CHARACTER
TRANSPORTATION_GROUP NEXT 12 CHARACTER
DIVISION NEXT 6 CHARACTER
GR_GI_QTY NEXT 42 CHARACTER
SQL string for column : "TO_NUMBER(:gr_gi_qty,'9999999990.000S')"
PRODUCT_HIERARCHY NEXT 54 CHARACTER
QM_FLAG NEXT 3 CHARACTER
VAR_ORDER_UNIT NEXT 3 CHARACTER
CONFIG_MATERIAL_FLAG NEXT 3 CHARACTER
BATCH_MGMT_FLAG NEXT 3 CHARACTER
SHELF_LIFE_DAYS NEXT 18 CHARACTER
SQL string for column : "TO_NUMBER(:shelf_life_days,'99990S')"
STORAGE_PCT NEXT 15 CHARACTER
SQL string for column : "TO_NUMBER(:storage_pct,'9990S')"
PROD_ALLOCATION_PROC NEXT 54 CHARACTER
CLIENT NEXT 9 CHARACTER
PROCUREMENT_RULE NEXT 3 CHARACTER
SOURCE_OF_SUPPLY NEXT 3 CHARACTER
TEMP_CONDITIONS NEXT 6 CHARACTER
PLATES_IN_KIT NEXT 12 CHARACTER
PRODN_UNIT NEXT 9 CHARACTER
RD_PROJECT_CODE NEXT 90 CHARACTER
SQL string for column : "LTRIM(:rd_project_code, '0')"
RD_PROJECT_DESC NEXT 90 CHARACTER
SQL string for column : "LTRIM(:rd_project_desc, ' ')"
COMPLEMENTARY_PART_SIOP_GROUP NEXT 90 CHARACTER
SQL string for column : "LTRIM(:complementary_part_siop_group, ' ')"
COMPLEMENTARY_PART NEXT 30 CHARACTER
SQL string for column : "LTRIM(:complementary_part, ' ')"
Record 1: Rejected - Error on table STG_MFG_AI_MATERIAL_GENERAL, column CREATE_DATE.
ORA-01830: date format picture ends before converting entire input string
Record 2: Rejected - Error on table STG_MFG_AI_MATERIAL_GENERAL, column CREATE_DATE.
ORA-01830: date format picture ends before converting entire input string
Record 3: Rejected - Error on table STG_MFG_AI_MATERIAL_GENERAL, column CREATE_DATE.
ORA-01830: date format picture ends before converting entire input string
Record 4: Rejected - Error on table STG_MFG_AI_MATERIAL_GENERAL, column CREATE_DATE.
ORA-01830: date format picture ends before converting entire input string
CONTROL FILE)
cat MARA.ctl
OPTIONS (SKIP=1, SILENT=(FEEDBACK))
LOAD DATA
CHARACTERSET UTF8
LENGTH SEMANTICS CHAR
BADFILE '$DATAPATH/MARA.bad'
TRUNCATE INTO TABLE stg_mfg_ai_material_general
(
material_number char (18), -- "LTRIM(:material_number, '0')",
create_date date "YYYYMMDD"
"decode(:create_date,'00000000',NULL,:create_date)",
created_by char (12),
modify_date date "YYYYMMDD"
"decode(:modify_date,'00000000',NULL,:modify_date)",
modified_by char (12),
deletion_flag char (1),
material_type char (4),
material_group char (9),
old_material_number char (18),
base_unit_of_measure char (3),
order_unit char (3),
basic_material char (14),
iso_desc char (18),
lab_design_office char (3),
purchasing_value_key char (4),
gross_weight decimal external (14) "TO_NUMBER(:gross_weight,'9999999990.000S')",
net_weight decimal external (14) "TO_NUMBER(:net_weight,'9999999990.000S')",
unit_of_weight char (3),
volume decimal external (14) "TO_NUMBER(:volume,'9999999990.000S')",
volume_unit char (3),
transportation_group char (4),
division char (2),
gr_gi_qty decimal external (14) "TO_NUMBER(:gr_gi_qty,'9999999990.000S')",
product_hierarchy char (18),
qm_flag char (1),
var_order_unit char (1),
config_material_flag char (1),
batch_mgmt_flag char (1),
shelf_life_days integer external (6) "TO_NUMBER(:shelf_life_days,'99990S')",
storage_pct integer external (5) "TO_NUMBER(:storage_pct,'9990S')",
prod_allocation_proc char (18),
client char (3),
procurement_rule char (1),
source_of_supply char (1),
temp_Conditions char (2),
plates_in_kit integer external (4),
prodn_unit char (3),
rd_project_code char (30) "LTRIM(:rd_project_code, '0')",
rd_project_desc char (30) "LTRIM(:rd_project_desc, ' ')"
,complementary_part_siop_group char (30) "LTRIM(:complementary_part_siop_group, ' ')"
,complementary_part char (10) "LTRIM(:complementary_part, ' ')"
V$nls_parameters
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
Also just before user invoke sqlldr they set LANG=en_US.utf8
npsv04 23: cat $DATAPATH/ZEW9B997_new.dat
MARA
CP048350 20011012BROADBDH 20200508GREENDL HALBTEA ST 100Z1 0.100+ 0.100+KG 0.000+ 900102 0.000+TEA5J16 X 0+ 0+ 00
CP049-100 20030314BROADBDH 20200414TANP17315 HALBHTS ST CP049-100 120Z1 0.000+ 0.000+KG 0.000+ 900102 0.000+HTSB2059 X 0+ 0+ 00
CP050050 20011012BROADBDH 20200508GREENDL HALBUVS ST 100Z1 0.000+ 0.000+KG 0.000+ 900102 0.000+UVS5J15 X 0+ 0+ 00
CP050640 20011012BROADBDH 20160623BOZEKAN HALBUVS FT 100Z1 0.000+ 0.000+KG 0.000+ 02 0.000+PUF X 0+ 0+ 00
CP050740 20011012BROADBDH 20200131GREENDL HALBAA ST 100Z1 0.000+ 0.000+KG 0.000+ 900102 0.000+AA 5J09 X 0+ 0+
Is it possible there are some bad characters in the file? For example, I took your data and did a cut-down load
SQL> create table stg (
2 material_number varchar2(18),
3 create_date date,
4 created_by varchar2(12),
5 modify_date date,
6 modified_by varchar2(12),
7 deletion_flag varchar2(1),
8 material_type varchar2(4),
9 material_group varchar2(9)
10 );
Table created.
OPTIONS (SKIP=1,CHARACTERSET=UTF8,LENGTH SEMANTICS CHAR)
LOAD DATA
INFILE *
BADFILE 'c:\temp\MARA.bad'
TRUNCATE
INTO TABLE stg
(
material_number char (18), -- "LTRIM(:material_number, '0')",
create_date date "YYYYMMDD" "decode(:create_date,'00000000',NULL,:create_date)",
created_by char (12),
modify_date date "YYYYMMDD" "decode(:modify_date,'00000000',NULL,:modify_date)",
modified_by char (12),
deletion_flag char (1),
material_type char (4),
material_group char (9)
)
BEGINDATA
MARA
CP048350 20011012BROADBDH 20200508GREENDL HALBTEA
CP049-100 20030314BROADBDH 20200414TANP17315 HALBHTS
CP050050 20011012BROADBDH 20200508GREENDL HALBUVS
SQL*Loader: Release 19.0.0.0.0 - Production on Fri Jun 5 16:41:47 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.
Control File: c:\temp\mara.ctl
Data File: c:\temp\mara.ctl
Bad File: c:\temp\MARA.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 250 rows, maximum of 1048576 bytes
Continuation: none specified
Path used: Conventional
Table STG, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MATERIAL_NUMBER FIRST 18 CHARACTER
CREATE_DATE NEXT 8 DATE YYYYMMDD
SQL string for column : "decode(:create_date,'00000000',NULL,:create_date)"
CREATED_BY NEXT 12 CHARACTER
MODIFY_DATE NEXT 8 DATE YYYYMMDD
SQL string for column : "decode(:modify_date,'00000000',NULL,:modify_date)"
MODIFIED_BY NEXT 12 CHARACTER
DELETION_FLAG NEXT 1 CHARACTER
MATERIAL_TYPE NEXT 4 CHARACTER
MATERIAL_GROUP NEXT 9 CHARACTER
Table STG:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 22500 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Jun 05 16:41:47 2020
Run ended on Fri Jun 05 16:41:47 2020
Elapsed time was: 00:00:00.44
CPU time was: 00:00:00.17
So far so good....
Then I replaced a couple of the spaces with a tab in the first row. The data *looks* identical but ....
SQL*Loader: Release 19.0.0.0.0 - Production on Fri Jun 5 16:44:02 2020
Version 19.6.0.0.0
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.
Control File: c:\temp\mara2.ctl.txt
Data File: c:\temp\mara2.ctl.txt
Bad File: c:\temp\MARA.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 250 rows, maximum of 1048576 bytes
Continuation: none specified
Path used: Conventional
Table STG, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MATERIAL_NUMBER FIRST 18 CHARACTER
CREATE_DATE NEXT 8 DATE YYYYMMDD
SQL string for column : "decode(:create_date,'00000000',NULL,:create_date)"
CREATED_BY NEXT 12 CHARACTER
MODIFY_DATE NEXT 8 DATE YYYYMMDD
SQL string for column : "decode(:modify_date,'00000000',NULL,:modify_date)"
MODIFIED_BY NEXT 12 CHARACTER
DELETION_FLAG NEXT 1 CHARACTER
MATERIAL_TYPE NEXT 4 CHARACTER
MATERIAL_GROUP NEXT 9 CHARACTER
Record 1: Rejected - Error on table STG, column CREATE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Table STG:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 22500 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 3
Total logical records rejected: 1
Total logical records discarded: 0
Run began on Fri Jun 05 16:44:02 2020
Run ended on Fri Jun 05 16:44:03 2020
Elapsed time was: 00:00:00.33
CPU time was: 00:00:00.22
But with the data you have sent me, I can't get this to fail on 12.2 or 19c on either Windows or Linux.