通过sqlldr导入数据到oracle

建表：

  create table system.COMMUNITY_PRICE(
    "CITY_CODE" NUMBER(6,0) NOT NULL ENABLE, 
	"COURT_ID"  NUMBER(6,0) NOT NULL ENABLE, 
	"COURT_NAME" VARCHAR2(200), 
	"COURT_ADDR" VARCHAR2(255), 
	"HOUSE_TYPE" VARCHAR2(10), 
	"DISTRICTFULLNAME" VARCHAR2(200),
	"COMPLETE_DATE" NUMBER(4,0), 
	"C_LONGITUDE" NUMBER(18,8), 
	"C_LATITUDE" NUMBER(18,8), 
	"PRICE_DATE" VARCHAR2(8),
	"PRICE" NUMBER(18,2)
  )

ctl文件：

OPTIONS  (direct=true,BINDSIZE=2048000,ERRORS=999999999,ROWS=44000,SILENT=(FEEDBACK , DISCARDS))

LOAD DATA characterset zhs16gbk



TRUNCATE INTO TABLE COMMUNITY_PRICE

FIELDS TERMINATED BY '|'

TRAILING NULLCOLS(
city_code        CHAR(6) NULLIF (city_code=BLANKS),  
court_id         CHAR(14) NULLIF (court_id=BLANKS),
court_name       CHAR(300) NULLIF (court_name=BLANKS),
court_addr       CHAR(400) NULLIF (court_addr=BLANKS),
HOUSE_TYPE       CHAR(100) NULLIF (HOUSE_TYPE=BLANKS),
complete_date    DECIMAL EXTERNAL NULLIF(complete_date=NULL),
DISTRICTFULLNAME CHAR(300) NULLIF (DISTRICTFULLNAME=BLANKS),
C_LONGITUDE      DECIMAL EXTERNAL NULLIF(C_LONGITUDE=BLANKS),
c_latitude       DECIMAL EXTERNAL NULLIF(c_latitude=BLANKS),
"PRICE_DATE"     "REPLACE(:PRICE_DATE,CHR(13))",
"PRICE"          DECIMAL EXTERNAL NULLIF(c_latitude=BLANKS)
)

数据文件：

通过concat_ws拼接 注意字段为NULL是使用IFNULL（）,不然会出错。

命令导入：

sqlldr userid=username/password control=D:/1/COMMUNITY_PRICE.ctl log=D:/1/test.log data=D:/1/test.bin rows=10

注意：中文如果出错。查看数据文件字符集、oracle字符集