oracle外部表加载excel的步骤

外部表（external tables）是对sql*loader 的一个补充，可以让我们访问数据库外部的数据资源。不过外部表存在着诸多的限制，所以我们使用前必须对其有充分的了解。外部表中的一些限制：

1、不能在外部表上执行DML操作。

2、不能在外部表上创建index。

3、不支持analyze 分析语句，但是可以使用dbms_stats。

4、不支持虚拟列。

ref:http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4347075367803

oracle 10g 以后可以允许我们把数据库中的表，write out 成为外部表。

外部表创建前的准备工作

在创建外部表前我们需要做一些准备工作。

1、在OS 上面创建一个目录，或者选择一个已经存在的目录。

2、确保上面创建的目录安装oracle 数据库的用户有读写的权利(这一点在类Unix 系统中需要注意)。

3、把外部表的数据实际数据放置在这个目录下。

930070350,smith,1111@qq.com,m,23

930070351,tom,2222@qq.com,m,27

930070352,peter,3333@qq.com,m,24

930070353,jack,4444@qq.com,m,25

930070354,king,5555@qq.com,m,26

将以上数据填充进ms-excel 保存为students_info.csv

4、在数据库系统中创建目录对象（默认情况下拥有DBA权限的用户才能创建目录对象）。

SQL> show user

USER is "SYS"

SQL> create or replace directory data_dir

  2  as 'c:\sqlloader\external';

Directory created.

5、把目录对象的read,write 权限授予给需要使用该目录对象的用户。

SQL> grant read,write on directory data_dir to hr;

Grant succeeded.

创建外部表

做完了上述的准备工作，我们就可以来创建外部表了。

SQL> ed

Wrote file afiedt.buf

  1  create table students(-- 外部表的创建语句和通常的create table 语句大部分相同。

  2  id     varchar2(16),

  3  name   varchar2(30),

  4  email  varchar2(20),

  5  gender char(1),

  6  age    number(2)

  7  )

  8  organization external--指定为外部表。

  9  ( type oracle_loader-- 指定外部表的加载类型。有oracle_loader,oracle_datapump。

 10    default directory data_dir--指定外部表默认的读写位置，是通过目录对象指定的，而不是实际的目录。

 11    access parameters--指定根据什么规则把外部文件中的数据加载到外部表中。

 12    ( records delimited by newline

 13      fields terminated by ','

 14    )

 15    location ('students_info.csv')--指定外部表加载的数据源的位置。

 16* )

SQL> /

Table created.

ref：http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm#i1012231

  http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables013.htm#ADMIN01507

在创建外部表的过程中，并不会实际的加载外部数据源中的数据，也不会验证access parameter 参数的有效性，这些动作要等到向外部表发出查询的时候才执行。

SQL> select * from students;

ID         NAME       EMAIL        GEND        AGE

---------- ---------- ------------ ---- ----------

930070350  smith      1111@qq.com  m            23

930070351  tom        2222@qq.com  m            27

930070352  peter      3333@qq.com  m            24

930070353  jack       4444@qq.com  m            25

930070354  king       5555@qq.com  m            26

使用oracle_loader 方式加载外部数据的时候，根据不同的情况像sql*loader 中一样也会生成 log,bad,discard file.当加载出现错误的时候，查看log文件非常重要。

现在我们可以访问students 中的数据，但是不能在students 表中执行DML操作和创建索引，这限制了students 表的用处，把students 表中的数据再加载到普通的oracle table 中来解决这个问题。

SQL> create index students_idx on students(id);

create index students_idx on students(id)

                             *

ERROR at line 1:

ORA-30657: 操作在外部组织表上不受支持

SQL> select * from students_info;

ID         NAME       EMAIL        GEND        AGE

---------- ---------- ------------ ---- ----------

930070350  smith      1111@qq.com  m            23

930070351  tom        2222@qq.com  m            27

930070352  peter      3333@qq.com  m            24

930070353  jack       4444@qq.com  m            25

930070354  king       5555@qq.com  m            26

SQL> create index students_info_idx on students_info(id);

Index created.

也就是说可以借助外部表把oracle 数据库外的数据加载进oracle 数据库中的普通表中。

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

转载于:http://blog.itpub.net/26451781/viewspace-752333/