SQL loader using OPTIONALLY ENCLOSED BY '"' for null value question
819159Feb 9 2012 — edited Feb 9 2012I found when I use OPTIONALLY ENCLOSED BY '"' in control file, I got ORA-01722: invalid number error when load field is null. database is 10g. I am wondering why.
-- table
CREATE TABLE "LOAD_G"
(
"G_ID" NUMBER(20,0) NOT NULL ENABLE,
"PRIMARY_EXT_ID" NUMBER(20,0),
"PRIMARY_EXT_ACC" VARCHAR2(32 BYTE) NOT NULL ENABLE,
"G_NAME" VARCHAR2(256 BYTE),
"G_SYMBOL" VARCHAR2(32 BYTE)
);
-- control file
OPTIONS(BINDSIZE=2000000, DIRECT=false )
LOAD DATA
TRUNCATE
INTO TABLE LOAD_G
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
G_ID,
PRIMARY_EXT_ID INTEGER EXTERNAL NULLIF PRIMARY_EXT_ID=BLANKS,
PRIMARY_EXT_ACC,
G_NAME,
G_SYMBOL
)
-- data - the second column in data file is null or blank, when load using above control file I got following error:
Record 1: Rejected - Error on table LOAD_G, column PRIMARY_EXT_ID.
ORA-01722: invalid number
if I remove <OPTIONALLY ENCLOSED BY '"'> from control file, the data file is loaded ok.
Edited by: user7435395 on Feb 9, 2012 11:38 AM