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.
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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')"
then getting error
EDW_DATE NEXT * , CHARACTER
SQL string for column : "TO_DATE(:EDW_DATE, 'MM-DD-YYYY HH24:MI:SS.FF')"
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks a lot Slight. The issue is resolved,
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
LOAD DATA
INFILE '/u01/dbadmin/edw/external
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,2121
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;