Oracle外部表小例

最新推荐文章于 2021-07-01 15:15:30 发布

最新推荐文章于 2021-07-01 15:15:30 发布 · 69 阅读

文章标签：

#数据库

1)建立操作系统目录e:/test，准备数据文件dept.txt并置于e:/test之下

"10","ACCOUNTING","NEW YORK"
"20","RESEARCH","DALLAS"
"30","SALES","CHICAGO"
"40","OPERATIONS","BOSTON"

2)创建数据库目录对象e_test

CREATE directory e_test AS 'e:/test';

3)定义外部表

CREATE TABLE new_dept(deptno NUMBER(2)
,dname VARCHAR2(14)
,loc VARCHAR2(13))
organization EXTERNAL(TYPE oracle_loader
DEFAULT directory e_test
access PARAMETERS(records delimited BY newline
badfile 'dept.bad'
discardfile 'dept.dsc'
logfile 'dept.log'
fields terminated BY ','
optionally enclosed BY '"'
missing field VALUES are NULL)
location('dept.txt'));

4)验证（支持查询，但不支持DML进行写操作）

SELECT * FROM new_dept;