通过外部表利用存储过程加载数据

每天需要定时百万条数据入库
建立外部表的步骤：

1、创建以“,”分隔的文件“all.csv”至“D:\Test”

2、创建一个Directory：

create directory TestTable_dir as 'D:\Test' ;
3 授权

grant read，write on directory to user;
4,建表

CREATE TABLE test_all (
t_name varchar2(50),
t_id VARCHAR2(50),
lo_du number(10),
lo_fen number(10),
lo_miao number(10，1),
la_du number(10),
la_fen number(10),
la_miao number(10，1),
v_level varchar2(50))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY datatest
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE 'all_bad_file.bad'
LOGFILE 'all_log_file.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(t_name,t_id,lo_du,lo_fen,lo_miao,la_du,la_fen,la_miao,v_level))
LOCATION ('all.csv')
)
PARALLEL
REJECT LIMIT 0
NOMONITORING

5， 查询 select * from test_all

6.创建存储过程

建外部表
create or replace procedure proc_create_ext_tbls authid current_user is
begin
dbms_utility.exec_ddl_statement( '
CREATE TABLE test_all (
t_name varchar2(50),
t_id VARCHAR2(50),
lo_du number(10),
lo_fen number(10),
lo_miao number(10),
la_du number(10),
la_fen number(10),
la_miao number(10),
v_level varchar2(50))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY datatest
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE' ||''''||'all_bad_file.bad'||''''||
'LOGFILE' ||''''||'all_log_file.log'||''''||
'FIELDS TERMINATED BY'|| ''''||','||''''||
'MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(t_name,t_id,lo_du,lo_fen,lo_miao,la_du,la_fen,la_miao,v_level))
LOCATION ('||''''||'all.csv'||''''||')
)
PARALLEL
REJECT LIMIT 0
NOMONITORING
');
end proc_create_ext_tbls;

存入数据库

create or replace procedure test_ddl
is
begin
insert into tower_dll
(tower_id,tower_name,latitude,longtitude,v_level)
SELECT
SEQUENCE.nextval,t_name||t_id,la_du+la_fen/60+la_miao/3600,lo_du+lo_fen/60+lo_miao/3600,v_level from test_all;
commit;
end test_ddl;


6 建job 定时执行

begin
sys.dbms_job.submit(job => :job,
what => 'proc_create_ext_tbls;',
next_date => to_date('28-06-2012 17:52:58', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+1/24/60*5');
commit;
end;

执行job时可能会出现视图错误。需要赋予相应权限
grant CREATE ANY TABLE,CREATE any sequence to user;