自己创建了文件ext_data.dat内容如下
D:\oracle\product\10.2.0\ext>type ext_data.dat
001,Wanglin,ChengDu
002,CaiWs,ChengDu
003,FangMx,Chongqing
001,Wanglin,ChengDu
002,CaiWs,ChengDu
003,FangMx,Chongqing
D:\oracle\product\10.2.0\ext>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on 星期三 3月 10 21:27:52 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
创建用户 建立DIRECTORY并授权
SQL> create user wanglin identified by wanglin;
用户已创建。
SQL> grant connect,resource to wanglin;
授权成功。
SQL> create DIRECTORY ext AS 'D:\oracle\product\10.2.0\ext';
目录已创建。
SQL> grant read,write on directory ext to wanglin;
授权成功。
SQL> conn wanglin/wanglin
已连接。
已连接。
创建外部表
SQL> CREATE TABLE ext_test(
2 id NUMBER(4),
3 name VARCHAR2(20),
4 addr VARCHAR2(20))
5 ORGANIZATION EXTERNAL(
6 TYPE ORACLE_LOADER DEFAULT DIRECTORY ext
7 ACCESS PARAMETERS(
8 records delimited by newline fields terminated by ','
9 missing field values are null(
10 id,name,addr)
11 ) LOCATION('ext_data.dat')
12 );
SQL> CREATE TABLE ext_test(
2 id NUMBER(4),
3 name VARCHAR2(20),
4 addr VARCHAR2(20))
5 ORGANIZATION EXTERNAL(
6 TYPE ORACLE_LOADER DEFAULT DIRECTORY ext
7 ACCESS PARAMETERS(
8 records delimited by newline fields terminated by ','
9 missing field values are null(
10 id,name,addr)
11 ) LOCATION('ext_data.dat')
12 );
表已创建。
查询表结构
SQL> desc ext_test
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER(4)
NAME VARCHAR2(20)
ADDR VARCHAR2(20)
NAME VARCHAR2(20)
ADDR VARCHAR2(20)
查询表内容
SQL> select * from ext_test;
ID NAME ADDR
---------- -------------------- --------------------
1 Wanglin ChengDu
2 CaiWs ChengDu
3 FangMx Chongqing
---------- -------------------- --------------------
1 Wanglin ChengDu
2 CaiWs ChengDu
3 FangMx Chongqing
测试insert
SQL> insert into ext_test values (4,'LIN','BeiJing');
insert into ext_test values (4,'LIN','BeiJing')
*
第 1 行出现错误:
ORA-30657: 操作在外部组织表上不受支持
SQL> insert into ext_test values (4,'LIN','BeiJing');
insert into ext_test values (4,'LIN','BeiJing')
*
第 1 行出现错误:
ORA-30657: 操作在外部组织表上不受支持
OS级别手动添加内容
SQL> host type ext_data.dat
001,Wanglin,ChengDu
002,CaiWs,ChengDu
003,FangMx,Chongqing
004,'LIN','BeiJing'
SQL> select * from ext_test;
001,Wanglin,ChengDu
002,CaiWs,ChengDu
003,FangMx,Chongqing
004,'LIN','BeiJing'
SQL> select * from ext_test;
ID NAME ADDR
---------- -------------------- --------------------
1 Wanglin ChengDu
2 CaiWs ChengDu
3 FangMx Chongqing
4 'LIN' 'BeiJing'
---------- -------------------- --------------------
1 Wanglin ChengDu
2 CaiWs ChengDu
3 FangMx Chongqing
4 'LIN' 'BeiJing'
查询建立表的DDL及结构
SQL> SET SERVEROUTPUT ON
SQL> SET LINESIZE 1000
SQL> SET FEEDBACK OFF
SQL> SET LONG 999999
SQL> SET PAGESIZE 1000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EXT_TEST') FROM DUAL;
SQL> SET LINESIZE 1000
SQL> SET FEEDBACK OFF
SQL> SET LONG 999999
SQL> SET PAGESIZE 1000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EXT_TEST') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','EXT_TEST')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
CREATE TABLE "WANGLIN"."EXT_TEST"
( "ID" NUMBER(4,0),
"NAME" VARCHAR2(20),
"ADDR" VARCHAR2(20)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "EXT"
ACCESS PARAMETERS
( records delimited by newline fields terminated by ','
missing field values are null(
id,name,addr)
)
LOCATION
( 'ext_data.dat'
)
)
转载于:https://blog.51cto.com/lovekde/282588