编号ORA-01722无效，数据正确

Question

EXECUTE SQLDRI之后的日志错误我试图在字段中插入数值，但我一直收到相同的错误INVALID NUMBER ORA-01722。我不知道错误在哪里，因为我正在为字段IMP_NPVFRONT插入声明为number(18,6(的十进制数字，并且该值被强制转换为这个长度，但它不起作用。我使用的是这个代码：

echo "OPTIONS (SKIP=1)" >> $TEPR_NFICHCONTROLCTL
echo "LOAD DATA" >> $TEPR_NFICHCONTROLCTL
echo "APPEND" >> $TEPR_NFICHCONTROLCTL
echo "INTO TABLE TERM.TTERMNZI" >> $TEPR_NFICHCONTROLCTL
echo "FIELDS TERMINATED BY ';' ">> $TEPR_NFICHCONTROLCTL
echo "OPTIONALLY ENCLOSED BY '"' " >> $TEPR_NFICHCONTROLCTL
echo "TRAILING NULLCOLS" >> $TEPR_NFICHCONTROLCTL
echo "(" >> $TEPR_NFICHCONTROLCTL
echo " COD_CURRPL CHAR NULLIF COD_CURRPL=""," >> $TEPR_NFICHCONTROLCTL
echo " COD_IDINST INTEGER EXTERNAL," >> $TEPR_NFICHCONTROLCTL
echo " COD_COUNTER CHAR NULLIF COD_COUNTER=""," >> $TEPR_NFICHCONTROLCTL
echo " XTI_INTERNA CHAR NULLIF XTI_INTERNA=""," >> $TEPR_NFICHCONTROLCTL
echo " COD_PRTFOLIO CHAR NULLIF COD_PRTFOLIO="",">> $TEPR_NFICHCONTROLCTL
echo " COD_FAMILY CHAR NULLIF COD_FAMILY=""," >> $TEPR_NFICHCONTROLCTL
echo " COD_GRUPO CHAR NULLIF COD_GRUPO=""," >> $TEPR_NFICHCONTROLCTL
echo " COD_TIPOPER CHAR NULLIF COD_TIPOPER=""," >> $TEPR_NFICHCONTROLCTL
echo " FEC_EXPIRY DATE "DD/MM/RRRR"," >> $TEPR_NFICHCONTROLCTL
echo " COD_STATUS CHAR NULLIF COD_STATUS=""," >> $TEPR_NFICHCONTROLCTL
echo " FEC_INICIO   DATE "DD/MM/RRRR"," >> $TEPR_NFICHCONTROLCTL
echo " COD_INSTRMNT CHAR NULLIF COD_INSTRMNT=""," >> $TEPR_NFICHCONTROLCTL
echo " COD_ENTITY CHAR NULLIF COD_ENTITY=""," >> $TEPR_NFICHCONTROLCTL
echo " IMP_NONFMV CHAR "to_number(:IMP_NONFMV, '999999999999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')"," >> $TEPR_NFICHCONTROLCTL
echo " IMP_CAMBIO CHAR "to_number(:IMP_CAMBIO, '999999999999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')","  >> $TEPR_NFICHCONTROLCTL
echo " COD_APLICFO CHAR,"  >> $TEPR_NFICHCONTROLCTL
echo " IMP_NPVFRONT CHAR "to_number(:IMP_NPVFRONT,'999999999999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')"," >> $TEPR_NFICHCONTROLCTL
echo " FEC_PROCESO "to_date('$3', 'DD/MM/RRRR')" ," >> $TEPR_NFICHCONTROLCTL
echo " COD_FICHERIN CONSTANT "$4" ," >> $TEPR_NFICHCONTROLCTL
echo " FEC_LOADER SYSDATE ," >> $TEPR_NFICHCONTROLCTL
echo " XTI_ESTADO CONSTANT 0 " >> $TEPR_NFICHCONTROLCTL
echo " )" >> $TEPR_NFICHCONTROLCTL
echo "$TEPR_HLOG Fichero de control $TEPR_NFICHCONTROLCTL generado correctamente"
echo "$TEPR_HLOG Fin Ejecucion de: $TEPR_NSCRIPT"
exit 0

上面生成了一个控制文件，其中包含这样的内容：

OPTIONS (SKIP=1)
LOAD DATA
APPEND
INTO TABLE TERM.TTERMNZI
FIELDS TERMINATED BY ';' 
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS
(
COD_CURRPL CHAR NULLIF COD_CURRPL="",
COD_IDINST INTEGER EXTERNAL,
COD_COUNTER CHAR NULLIF COD_COUNTER="",
XTI_INTERNA CHAR NULLIF XTI_INTERNA="",
COD_PRTFOLIO CHAR NULLIF COD_PRTFOLIO="",
COD_FAMILY CHAR NULLIF COD_FAMILY="",
COD_GRUPO CHAR NULLIF COD_GRUPO="",
COD_TIPOPER CHAR NULLIF COD_TIPOPER="",
FEC_EXPIRY DATE "DD/MM/RRRR",
COD_STATUS CHAR NULLIF COD_STATUS="",
FEC_INICIO   DATE "DD/MM/RRRR",
COD_INSTRMNT CHAR NULLIF COD_INSTRMNT="",
COD_ENTITY CHAR NULLIF COD_ENTITY="",
IMP_NONFMV CHAR "to_number(:IMP_NONFMV, '999999999999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')",
IMP_CAMBIO CHAR "to_number(:IMP_CAMBIO, '999999999999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')",
COD_APLICFO CHAR,
IMP_NPVFRONT CHAR "to_number(:IMP_NPVFRONT,'999999999999999999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')",
FEC_PROCESO "to_date('', 'DD/MM/RRRR')" ,
COD_FICHERIN CONSTANT "" ,
FEC_LOADER SYSDATE ,
XTI_ESTADO CONSTANT 0 
)

提前谢谢你，对不起我的英语：(

创建表格：

CREATE TABLE TTERMNZI 
(
COD_IDINST VARCHAR2(150 BYTE) NOT NULL 
, COD_FICHERIN VARCHAR2(20 BYTE) 
, FEC_PROCESO DATE 
, COD_OPERORI VARCHAR2(100 BYTE) 
, XTI_ESTADO NUMBER(1, 0) 
, COD_ESTADO NUMBER(4, 0) 
, COD_PRTFOLIO VARCHAR2(20 BYTE) 
, COD_DEALID VARCHAR2(20 BYTE) 
, COD_EVENT VARCHAR2(20 BYTE) 
, COD_CURRCON VARCHAR2(20 BYTE) 
, COD_COUNTER VARCHAR2(20 BYTE) 
, FEC_ALTA DATE 
, FEC_MATURITY DATE 
, FEC_FRONTVAL DATE 
, FEC_RKVAL DATE 
, IMP_NPVFRNEU NUMBER(18, 6) 
, IMP_NPVBO NUMBER(18, 6) 
, IMP_NPVBOEU NUMBER(18, 6) 
, COD_CURRPL VARCHAR2(4 BYTE) 
, IMP_MVPAGO NUMBER(18, 6) 
, IMP_MVRECIBO NUMBER(18, 6) 
, IMP_MVCONSA NUMBER(18, 6) 
, IMP_MVCONEU NUMBER(18, 6) 
, COD_FAMILY VARCHAR2(20 BYTE) 
, COD_GRUPO VARCHAR2(20 BYTE) 
, COD_STATUS VARCHAR2(8 BYTE) 
, COD_TIPOPER VARCHAR2(4 BYTE) 
, FEC_EXPIRY DATE 
, FEC_INICIO DATE 
, COD_INSTRMNT VARCHAR2(20 BYTE) 
, COD_ENTITY VARCHAR2(20 BYTE) 
, COD_APLICFO VARCHAR2(5 BYTE) NOT NULL 
, FEC_LOADER DATE 
, COD_USUARIO VARCHAR2(5 BYTE) 
, XTI_INTERNA VARCHAR2(1 BYTE) 
, IMP_NONFMV NUMBER(18, 6) 
, IMP_CAMBIO NUMBER(18, 6) 
, IMP_NPVFRONT NUMBER(18, 6) 
, CONSTRAINT CTERMNZI PRIMARY KEY 
(
COD_IDINST 
, COD_APLICFO 
)
USING INDEX 
(
CREATE UNIQUE INDEX CTERMNZI ON TTERMNZI (COD_IDINST ASC, COD_APLICFO 
ASC) 
LOGGING 
TABLESPACE ETERM01 
PCTFREE 10 
INITRANS 2 
STORAGE 
( 
INITIAL 65536 
NEXT 1048576 
MINEXTENTS 1 
MAXEXTENTS UNLIMITED 
BUFFER_POOL DEFAULT 
) 
NOPARALLEL 
)
ENABLE 
) 
LOGGING 
TABLESPACE ETERM01 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
INITIAL 65536 
NEXT 1048576 
MINEXTENTS 1 
MAXEXTENTS UNLIMITED 
BUFFER_POOL DEFAULT 
) 
NOCOMPRESS 
NOPARALLEL;

数据文件：

EST;3773909;8EUK;N;RF_STARTY;IRD;BOND;S;18/10/2024;LIVE;25/09/2020;OCD 0 184534;0018234537;-2402029;1;0;MX;-2402029.000000
EST;8223589;A4805;N;SW_STARTY;IRD;IRS;B;22/12/2045;LIVE;29/09/2014;EUROPE;001345297;-217726.721335;1;0;MX;-217726.721335

Answer 1

问题似乎是IMP_NPVFRONT在两个样本记录中都具有值"MX"，导致在尝试对其应用to_number表达式时出错。

>COD_COUNTERXTI_INTERNACOD_PRTFOLIO>COD_GRUPOCOD_TIPOPERFEC_EXPIRYYIRD债券S<2024年10月18日td>-2400209.00000>NSW_STARTYIRDIRSB2045年12月22日现场2014年9月29日欧洲001345297-217726.721335<1>0-217726.721335

COD_CURRPL	COD_IDINST	COD_FAMILY	XTI_ESTADO
EST	3773909	8EUK	RF_STARTY	1	0	MX
EST	8223589	14885	MX