建表:
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字符集