oracle10G -- create external tables 將文字檔存成table 供資料庫存取

1.文字檔emp.dat
10000001,nina,FINANCE
10000002,nina2,FINANCE2
10000003,nina3,FINANCE3
10000004,nina4,FINANCE4

2.sqlplus 操作過程
Microsoft Windows [版本 5.2.3790]
(C) 版權所有 1985-2003 Microsoft Corp。

E:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 9月 27 22:46:32 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
已連線.
建立存放文字檔的目錄. 文字檔要放在這個目錄.
SQL> create or replace directory employee_data as 'C:\employee_data'
2 ;

已建立目錄.

SQL> create table employee_ext
2 (
3 empid number(8),
4 emp_name varchar2(30),
5 dept_name varchar2(20)
6 )
7 organization external
8 (type oracle_loader
9 default directory employee_data
10 access parameters
11 (
12 records delimited by newline
13 fields terminated by ','
14 missing field values are null
15 )
16 location('emp.dat')
17 )
18 ;

已建立表格.


SQL> select * from employee_ext
2 ;

EMPID EMP_NAME DEPT_NAME
---------- ------------------------------ --------------------
10000001 nina FINANCE
10000002 nina2 FINANCE2
10000003 nina3 FINANCE3
10000004 nina4 FINANCE4


3.相關view dba_external_tables