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
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot Slight. The issue is resolved,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;