Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Demetrius.

Asked: May 27, 2020 - 1:01 pm UTC

Last updated: June 05, 2020 - 9:19 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

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+                                                 




and Connor said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.