假如有这么一行数据,最后两列的数据其实都是NULL值,可是从MYSQL里导出来的时候,就导成了\N。
我现在想通过SQLLDR把这些数据导进ORACLE里,想在控制文件里增加判断。如果列值是\N,就插入空值。不知道该这么写?
这是我当前的控制文件的写法?
load data
INFILE "/home/oracle/weixh/stanley_outfile_20100908.csv"
truncate
into table subscription_detail
FIELDS TERMINATED BY ","
Optionally enclosed by '"'
TRAILING NULLCOLS
(ID,
SUBSCRIPTION_ID,
VACCOUNT_ID,
GMT_SCHEDULE_STRAT DATE "YYYY-MM-DD HH24:MI:SS" ,
GMT_SCHEDULE_END DATE "YYYY-MM-DD HH24:MI:SS",
STATUS,
ORDER_ID,
GMT_CREATE DATE "YYYY-MM-DD HH24:MI:SS",
GMT_MODIFIED DATE "YYYY-MM-DD HH24:MI:SS",
ORDER_ITEM_ID,
GMT_REAL,
COMMENTS,
PERSON_ID)
正确的写法为:
INFILE "/home/oracle/weixh/stanley_outfile_20100909.bad"
append
into table temp_subscription_detail
FIELDS TERMINATED BY ","
Optionally enclosed by '"'
TRAILING NULLCOLS
(ID,
SUBSCRIPTION_ID ,
VACCOUNT_ID nullif(VACCOUNT_ID=" \\N"),
GMT_SCHEDULE_STRAT DATE "YYYY-MM-DD HH24:MI:SS" nullif(GMT_SCHEDULE_STRAT=" \\N") ,
GMT_SCHEDULE_END DATE "YYYY-MM-DD HH24:MI:SS" nullif(GMT_SCHEDULE_END=" \\N") ,
STATUS,
ORDER_ID,
GMT_CREATE DATE "YYYY-MM-DD HH24:MI:SS",
GMT_MODIFIED DATE "YYYY-MM-DD HH24:MI:SS",
ORDER_ITEM_ID,
GMT_REAL,
COMMENTS nullif(COMMENTS=" \\N"),
PERSON_ID nullif(PERSON_ID=" \\N"))
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-672986/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-672986/