最近在使用sql loader进行数据的导入，顺便测试体验了一把oracle外部表的功能,如下：
SQL> conn / as sysdba
已连接。
会话已更改。
SQL> create or replace directory scott_dir as 'c:\scott';
目录已创建。
SQL> grant all on directory scott_dir to scott;
授权成功。
SQL> conn scott/tiger
已连接。
会话已更改。
SQL> create table test (id number,name varchar2(20),weight number);
表已创建。
SQL> create table ext_test(id number,name varchar2(20),weight number)
2 organization external
3 (
4 type oracle_loader
5 default directory scott_dir
6 access parameters
7 (records delimited by newline
8 badfile scott_dir:'scott%a_%p.bad'
9 logfile scott_dir:'scott%a_%p.log'
10 fields terminated by ','
11 missing field values are null
12 (id,name,weight)
13 )
14 location('test.csv')
15 )
16 parallel reject limit unlimited;
表已创建。
SQL> select * from ext_test;
ID NAME WEIGHT
---------- ------------------------------------------------------------ ----------
1 '张三' 70
2 '李四' 80
3 '王五' 90
4 '张飞' 120
SQL> select * from test;
未选定行
SQL> insert /*+ append */ into test select * from ext_test;
已创建4行。
SQL> select * from test;
select * from test
*
第 1 行出现错误:
ORA-12838: 无法在并行模式下修改之后读/修改对象
SQL> commit;
提交完成。
SQL> select * from test;
ID NAME WEIGHT
---------- ------------------------------------------------------------ ----------
1 '张三' 70
2 '李四' 80
3 '王五' 90
4 '张飞' 120
Test.csv内容：
1,'张三',70
2,'李四',80
3,'王五',90
4,'张飞',120
考虑到创建外部表的语法过于复杂，可以通过sqlloader来自动生成：
编辑control文件
load data infile 'test.csv' badfile 'test.bad' discardfile 'test.dsc' append into table test fields terminated by ',' optionally enclosed by '"' and '"' trailing nullcols (id char(4000), name char(4000), weight char(4000) )
PS C:\scott> sqlldr scott/tiger control=test.ctl external_table=GENERATE_ONLY
SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 10月 8 16:43:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
文件需要 CREATE DIRECTORY 语句
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'C:\scott'
用于外部表的 CREATE TABLE 语句:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_TEST"
(
"ID" NUMBER,
"NAME" VARCHAR2(20),
"WEIGHT" NUMBER
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.bad'
DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.dsc'
LOGFILE 'test.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(4000)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"NAME" CHAR(4000)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"WEIGHT" CHAR(4000)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """
)
)
location
(
'test.csv'
)
)REJECT LIMIT UNLIMITED
用于加载内部表的 INSERT 语句:
------------------------------------------------------------------------
INSERT /*+ append */ INTO TEST
(
ID,
NAME,
WEIGHT
)
SELECT
"ID",
"NAME",
"WEIGHT"
FROM "SYS_SQLLDR_X_EXT_TEST"
用于清除由以前的语句创建的对象的语句:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_TEST"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-708767/ ，如需转载，请注明出处，否则将追究法律责任。
