INVALIDNUMBERORA-01722具有正確的資料-有解無憂

執行 SQLDR 后的日志錯誤我試圖在欄位中插入數值，但我一直收到相同的錯誤 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

uj5u.com熱心網友回復：

問題似乎是 IMP_NPVFRONT 在兩個示例記錄中都具有值“MX”，從而在嘗試對其應用to_number運算式時導致錯誤。

COD_CURRPL	COD_IDINST	貨到付款計數器	XTI_INTERNA	COD_PRTFOLIO	COD_FAMILY	COD_GRUPO	COD_TIPOPER	FEC_EXPIRY	COD_STATUS	FEC_INICIO	COD_INSTRMNT	COD_ENTITY	IMP_NONFMV	IMP_CAMBIO	COD_APLICFO	IMP_NPVFRONT	FEC_PROCESO	COD_FICHERIN	FEC_LOADER	XTI_ESTADO
美東時間	3773909	8EUK	N	RF_STARTY	稅務局	鍵	秒	18/10/2024	居住	25/09/2020	強迫癥 0 184534	0018234537	-2402029	1	0	MX	-2402029.000000
美東時間	8223589	A4805	N	SW_STARTY	稅務局	國稅局	乙	22/12/2045	居住	29/09/2014	歐洲	001345297	-217726.721335	1	0	MX	-217726.721335

當我設定 IMP_NPVFRONT filler（所以它被跳過）時，檔案被加載。您將需要檢查您的資料以及它應該如何加載。（我還在測驗檔案中添加了一個標題以匹配OPTIONS (SKIP=1)控制檔案。）

其他要點：

COD_CURRPL CHAR NULLIF COD_CURRPL="" 以及如果沒有值則設定為 null 的所有其他欄位 - 這似乎沒有實作任何目標，因此我洗掉了 'nullif' 運算式。
COD_IDINST INTEGER EXTERNAL- 此資料型別用于二進制資料，而不是表示數字的文本字符。老實說我從來沒有用過它。根據日志檔案，SQL*Loader 忽略了它，無論如何該列是一個字串。

OPTIONS (SKIP=1)
LOAD DATA
APPEND
INTO TABLE TTERMNZI
FIELDS TERMINATED BY ';' 
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS
(
 COD_CURRPL CHAR,
 COD_IDINST CHAR,
 COD_COUNTER CHAR,
 XTI_INTERNA CHAR,
 COD_PRTFOLIO CHAR,
 COD_FAMILY CHAR,
 COD_GRUPO CHAR,
 COD_TIPOPER CHAR,
 FEC_EXPIRY DATE "DD/MM/RRRR",
 COD_STATUS CHAR,
 FEC_INICIO DATE "DD/MM/RRRR",
 COD_INSTRMNT CHAR,
 COD_ENTITY CHAR,
 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 filler, --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 
 )

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/395805.html

標籤：sql 甲骨文

上一篇：如何使用bash腳本和SQLPLUS自動啟動Oracle19C？

下一篇：ORA-00927:嘗試更新物件關系資料庫中的表時缺少等號