ITPub博客

首页 > 数据库 > Oracle > 使用oracle外部表读取文件数据

使用oracle外部表读取文件数据

原创 Oracle 作者:cyr1974 时间:2007-05-18 16:18:01 0 删除 编辑

1 、创建目录及授权

create directory exttab as 'F:oracleproduct10.2.0oradataexttab'

GRANT READ ON DIRECTORY exttab TO system;
GRANT WRITE ON DIRECTORY exttab TO system;

2、准备数据文件到相关目录

F:oracleproduct10.2.0oradataexttab>dir
Volume in drive F has no label.
Volume Serial Number is 5B8D-A9E4

Directory of F:oracleproduct10.2.0oradataexttab

2007-05-18 16:09

.
2007-05-18 16:09 ..
2007-05-18 15:17 221 1.dat
2007-05-18 14:49 300 1.dat.bak
2007-05-18 15:17 156 2.dat
2007-05-18 14:49 234 2.dat.bak
2007-05-18 15:32 591 EXTTEST2_2292_3356.log
2007-05-18 15:18 84 EXTTEST_2292_3356.bad
2007-05-18 15:18 2,860 EXTTEST_2292_3356.log
2007-05-18 15:12 84 EXTTEST_2292_3964.bad
2007-05-18 15:12 1,430 EXTTEST_2292_3964.log
2007-05-18 15:13 84 EXTTEST_2292_588.bad
2007-05-18 15:13 1,430 EXTTEST_2292_588.log
2007-05-18 16:09 244 EXTTXTM_2292_3356.bad
2007-05-18 16:11 1,536 EXTTXTM_2292_3356.log
2007-05-18 15:27 81 test2.txt
2007-05-18 16:05 121,756 txtm.txt
15 File(s) 131,091 bytes

3、创建外部表

SQL> CREATE TABLE exttxtm
2 ( KEY_1 CHAR(31),
3 STAT CHAR(1),
4 TEXT CHAR(200),
5 TEXT_LENGTH CHAR(3),
6 TEXT_BOUND_LEN CHAR(3))
7 ORGANIZATION EXTERNAL
8 (TYPE ORACLE_LOADER
9 DEFAULT DIRECTORY exttab
10 ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
11 FIELDS TERMINATED BY ',')
12 LOCATION('txtm.txt'));

Table created.
4、校验数据是否可读

SQL> select * from exttxtm where rownum<2;

KEY_1 ST TEXT
TEXT_L TEXT_B
------------------------------ -- ----------------------------------------------
---- ------ ------
003BORACT010000000000008206000 0 NZD RESIDENTIAL INVESTMT LOAN
029 000
1

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/36779/viewspace-915500/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-16

  • 博文量
    37
  • 访问量
    256959