使用oracle外部表进行数据加载

最近在使用sql loader进行数据的导入，顺便测试体验了一把oracle外部表的功能,如下：

SQL> conn / as sysdba

已连接。

 

会话已更改。

 

SQL> create or replace directory scott_dir as 'c:\scott';

 

目录已创建。

SQL> grant all on directory scott_dir to scott;

 

授权成功。

 

SQL> conn scott/tiger

已连接。

 

会话已更改。

 

SQL> create table test (id number,name varchar2(20),weight number);

 

表已创建。

 

SQL> create table ext_test(id number,name varchar2(20),weight number)

  2  organization external

  3  (

  4    type oracle_loader

  5    default directory scott_dir

  6  access parameters

  7  (records delimited by newline

  8  badfile scott_dir:'scott%a_%p.bad'

  9  logfile scott_dir:'scott%a_%p.log'

 10  fields terminated by ','

 11  missing field values are null

 12  (id,name,weight)

 13  )

 14  location('test.csv')

 15  )

 16  parallel reject limit unlimited;

 

表已创建。

 

SQL> select * from ext_test;

 

        ID NAME                                                             WEIGHT

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

         1 '张三'                                                               70

         2 '李四'                                                               80

         3 '王五'                                                               90

         4 '张飞'                                                              120

 

SQL> select * from test;

 

未选定行

 

SQL> insert /*+ append */ into test select * from ext_test;

 

已创建4行。

 

SQL> select * from test;

select * from test

              *

第 1 行出现错误:

ORA-12838: 无法在并行模式下修改之后读/修改对象

 

 

SQL> commit;

 

提交完成。

 

SQL> select * from test;

 

        ID NAME                                                             WEIGHT

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

         1 '张三'                                                               70

         2 '李四'                                                               80

         3 '王五'                                                               90

         4 '张飞'                                                              120

 

 

Test.csv内容：

1,'张三',70

2,'李四',80

3,'王五',90

4,'张飞',120

考虑到创建外部表的语法过于复杂，可以通过sqlloader来自动生成：

编辑control文件

load data

infile 'test.csv'

badfile 'test.bad'

discardfile 'test.dsc'

append into table test

fields terminated by ','

optionally enclosed by '"' and '"'

trailing nullcols

(id char(4000),

name char(4000),

weight char(4000)

)

 

PS C:\scott> sqlldr scott/tiger control=test.ctl external_table=GENERATE_ONLY

 

SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 10月 8 16:43:51 2011

 

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

 

文件需要 CREATE DIRECTORY 语句

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

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'C:\scott'

 

 

用于外部表的 CREATE TABLE 语句:

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

CREATE TABLE "SYS_SQLLDR_X_EXT_TEST"

(

  "ID" NUMBER,

  "NAME" VARCHAR2(20),

  "WEIGHT" NUMBER

)

ORGANIZATION external

(

  TYPE oracle_loader

  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

  ACCESS PARAMETERS

  (

    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.bad'

    DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.dsc'

    LOGFILE 'test.log_xt'

    READSIZE 1048576

    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """  LDRTRIM

    MISSING FIELD VALUES ARE NULL

    REJECT ROWS WITH ALL NULL FIELDS

    (

      "ID" CHAR(4000)

        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,

      "NAME" CHAR(4000)

        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,

      "WEIGHT" CHAR(4000)

        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """

    )

  )

  location

  (

    'test.csv'

  )

)REJECT LIMIT UNLIMITED

 

 

用于加载内部表的 INSERT 语句:

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

INSERT /*+ append */ INTO TEST

(

  ID,

  NAME,

  WEIGHT

)

SELECT

  "ID",

  "NAME",

  "WEIGHT"

FROM "SYS_SQLLDR_X_EXT_TEST"

 

 

用于清除由以前的语句创建的对象的语句:

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

DROP TABLE "SYS_SQLLDR_X_EXT_TEST"

DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

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

转载于:http://blog.itpub.net/10972173/viewspace-708767/