将CSV导入Oracle数据库中的办法
在Plsql Developer中 export to csv 很方便，但是没有相应的逆向的import功能， 譬如某次对查询出来的结果export to csv做了保存，之后如果需要将该csv文件再导入数据库，对这部分查询数据再做处理，会不太方便。大约有40w数据。数据格式为：
"R1_1","R1_2","R4_1","R4_2","R12","H1","RDM","H1","RDM","R7_1","R7_2"
"艾","炳云","1944","8","2","331102101207002081","1","331102001006016074","1","3",""
"艾","程","1978","1","6","330204001008009032","2","330204004007015052","1","3",""
"艾","春丽","1978","10","3","330522100201010044","2","330522100037002170","1","2",""
方法1、在编辑器中替换 \n 为 \n insert into table_name values(
方法2、先导入Access数据库，然后导入Oracle
方法1面对数据量大时不能用。
其实可以用oracle的external table来处理，创建外部表，读取文本文件，像数据表那样处理。
第一步：创建Directory
create or replace directory dest_dir_name as 'G:\'
第二步：创建external table，读取数据文件
create table TEMP_12
(
R1_1VARCHAR2(255),
R1_2VARCHAR2(255),
R4_1VARCHAR2(255),
R4_2VARCHAR2(255),
R12 VARCHAR2(255),
H1 VARCHAR2(255),
RDM VARCHAR2(255),
H1_1VARCHAR2(255),
RDM_1 VARCHAR2(255),
R7_1VARCHAR2(255),
R7_2VARCHAR2(255)
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY dest_dir_name
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(R1_1, R1_2, R4_1, R4_2, R12, H1, RDM, H1_1, RDM_1, R7_1, R7_2))
LOCATION ('test20110217.csv')
)
PARALLEL
REJECT LIMIT 0
NOMONITORING;
页:
[1]