We help IT Professionals succeed at work.

Error in SQLLoader

dba1234
dba1234 asked
on
Hi ,
I am uploading data from flat file with the help of SQL Loader,But I am getting error
Record 39: Rejected - Error on table IHA.EDW_CLM_LINE_EXT, column EDW_DATE.
ORA-01843: not a valid month

But according to flat file and table the structure of the column is same.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Do you have to_date set up in the controlfile tp convert the string to a date?

If so,

Please provide the table structure, sqlldr controlfile and row 39 of the datafile.

Author

Commented:
Control file=
 LOAD DATA
INFILE '/u01/dbadmin/edw/external_file_tab/EDW_CLM_LINE_Test_Data.txt'
INTO TABLE IHA.EDW_CLM_LINE_ext
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  CLM_LINE_KEY             ,
  EDW_DATE                 ,
  CURR_ROW                 ,
  CLM_KEY                  ,
  CLM_NO                   ,
  CLM_LINE_NO              ,
  CLM_SRC_IND              ,
  CLM_TYPE_IND

Data file-
392801263,10/4/2011,Y,212142355,0108241106778,18,1,1,2011,H,P,,,,,,,,1,0,11122,2388183,155692,19938,4995958,20,,9/29/2011,9/29/2011,O,10/7/2011,3459399,10/1/2011,GL01,393,23705717,7/29/2011,7/29/2011,,,,-1,0,-400,-195.5,0,0,0,0,0,0,0,,0,0,0,-195.5,0,0,0,0,0,0,0,0,0,0,0,0,1692,,,,192,,,0,0,,,,,,,,,,,,,136800,,,,,1650,,,,,,8,8,7,,1012,,12,,,,,,68,,4152,10/3/2011,4152,12/31/9999,10/4/2011,10/4/2011,,,

Table structure

CREATE TABLE table_name
(
  CLM_LINE_KEY              NUMBER              NOT NULL,
  EDW_DATE                  varchar2(15 byte),
  CURR_ROW                  CHAR(1 BYTE)        DEFAULT 'Y'                   NOT NULL,
  CLM_KEY                   NUMBER,
  CLM_NO                    VARCHAR2(40 BYTE),
  CLM_LINE_NO               NUMBER(5),
  CLM_SRC_IND               VARCHAR2(10 BYTE),
  CLM_TYPE_IND              VARCHAR2(10 BYTE),
  PART_YEAR                 NUMBER(4)           NOT NULL,
  CLM_FORM_IND              VARCHAR2(10 BYTE),
  CLM_LINE_STAT_IND         VARCHAR2(10 BYTE),
  CLM_PROD_IND              VARCHAR2(10 BYTE),
  XTRL_CLM_NO               VARCHAR2(40 BYTE),
  XTRL_CLM_LINE_NO          NUMBER(5),
  ADJSMT_FROM_CLM_KEY       NUMBER,
  ADJSMT_FROM_CLM_LINE_KEY  NUMBER,
  ADJSMT_TO_CLM_KEY         NUMBER,
  ADJSMT_TO_CLM_LINE_KEY    NUMBER,
 
  INGRE_COST                NUMBER(13,2),
  DISPG_COST                NUMBER(13,2),
  DED_COST                  NUMBER(13,2),
  LAG_COST                  NUMBER(13,2),
  DX_KEY_1                  NUMBER,
  DX_KEY_2                  NUMBER,
  DX_KEY_3                  NUMBER,
  DX_KEY_4                  NUMBER,
  DX_CLS_KEY                NUMBER,
  INT_START_DATE            VARCHAR2(15 BYTE),
  INT_STOP_DATE          VARCHAR2(15 BYTE),
  ACCMLD_INT_DAYS           NUMBER(6),
  ORIGL_INT_DAYS            NUMBER(6),
  SURFACE_IND_1             VARCHAR2(10 BYTE),
  SURFACE_IND_2             VARCHAR2(10 BYTE),
  SURFACE_IND_3             CHAR(18 BYTE),
  SURFACE_IND_4             VARCHAR2(10 BYTE),
  SURFACE_IND_5             VARCHAR2(10 BYTE),
  NDC_KEY                   NUMBER,
  DUR_CNFLT_IND_1           VARCHAR2(10 BYTE),
  SIGNC_IND_1               VARCHAR2(10 BYTE),
  DUR_CNFLT_IND_2           VARCHAR2(10 BYTE),
  SIGNC_IND_2               VARCHAR2(10 BYTE),
  DUR_CNFLT_IND_3           VARCHAR2(10 BYTE),
  SIGNC_IND_3               VARCHAR2(10 BYTE),
  SERV_PROC_KEY             NUMBER,

  STOP_LOSS_CODE            VARCHAR2(15 BYTE),
  BEN_CODE_KEY              NUMBER,
  FUND_TYPE_KEY             NUMBER,
  PROV_ROLE_KEY             NUMBER,
  RBNDG_IND                 VARCHAR2(10 BYTE),
  CLM_CATG_KEY              NUMBER,
  SERV_TYPE_KEY             NUMBER,
  REQ_KEY                   NUMBER,
  REQ_SERV_LINE_KEY         NUMBER,
  ADJR_USR_KEY              NUMBER,
  ENTD_USR_KEY              NUMBER,
  CHNGD_USR_KEY             NUMBER,
  LAST_CHNG_DATE          VARCHAR2(15 BYTE),
  LAST_USR_KEY              NUMBER,
  EDW_EXP_DATE            VARCHAR2(15 BYTE),
  EDW_ADDED_DATE          VARCHAR2(15 BYTE),
  EDW_UPD_DATE             VARCHAR2(15 BYTE),
  TOOTH_NO                  VARCHAR2(10 BYTE),
  METRC_QTY                 NUMBER,
  QTY_DISPD                 NUMBER
)
TABLESPACE EDW
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>> EDW_DATE                  varchar2(15 byte)

The error means that column is a date but the definition shows varchar2.

There is a mismatch somewhere.

My guess is in the table.  Google: load date with sqlldr

There are examples of using to_date in the controlfile.

Author

Commented:
I am using the below parameter in the front of column "TO_DATE(:EDW_DATE, 'MM-DD-YYYY HH24:MI:SS.FF')",

then getting error


EDW_DATE                          NEXT     *   ,       CHARACTER
    SQL string for column : "TO_DATE(:EDW_DATE, 'MM-DD-YYYY HH24:MI:SS.FF')"
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
A couple things wrong.

first (mentioned above):  EDW_DATE is a VARCHAR2 in your table, why take the file value from a string, to a date then back to a string?

second and the main reason for the error, I see the value as '10/4/2011'

Your date mask is 'MM-DD-YYYY HH24:MI:SS.FF'.

Do you see the issue?  The formats need to match.

Author

Commented:
Thanks a lot Slight. The issue is resolved,
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Can you share the solution for anyone else having this issue?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.