Link to home
Create AccountLog in
Avatar of dba1234
dba1234

asked on

Error in SQLLoader

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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of dba1234
dba1234

ASKER

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;
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Avatar of dba1234

ASKER

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')"
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Avatar of dba1234

ASKER

Thanks a lot Slight. The issue is resolved,
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.