oracle_loader类型外部表

Jetbrains全系列IDE稳定放心使用

1、创建目录（create any directory）：

    SQL> create user oracle identified by oracle;

用户已创建。

SQL> grant dba to oracle;

授权成功。

SQL> grant create any directory to oracle;

授权成功。

SQL> conn oracle/oracle
已连接。

2、创建外部表：

SQL> select * from dba_directories;

OWNER                DIRECTORY_NAME                           DIRECTORY_PATH
——————– —————————————- ——————————————-
—
SYS                  ADMIN_DIR                                C:\ADE\aime_vista_ship\oracle/md/admin
SYS                  DATA_PUMP_DIR                            d:\oracle\product\10.2.0\admin\orcl\dpdump\
SYS                  BDUMP                                    D:\oracle\product\10.2.0\admin\orcl\bdump
SYS                  WORK_DIR                                 C:\ADE\aime_vista_ship\oracle/work

SQL> create table alert_log(text varchar2(400))
  2  organization external (
  3  type oracle_loader
  4  default directory BDUMP
  5  access parameters(
  6  records delimited by newline
  7  nobadfile
  8  nodiscardfile
  9  nologfile
 10  )
 11  location(‘alert_orcl.log’)
 12  )
 13  reject limit unlimited
 14  /

表已创建。

SQL> select * from  alert_log  where rownum<10;

TEXT
——————————————————————
Dump file d:\oracle\product\10.2.0\admin\orcl\bdump\alert_orcl.log
Sun May 16 11:25:15 2010
ORACLE V10.2.0.3.0 – Production vsnsta=0
vsnsql=14 vsnxtr=3
Windows NT Version V6.0
CPU                 : 2 – type 586
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:372M/1021M
Sun May 16 11:25:15 2010

已选择9行。

3、外部表acess paramter获得方式

   sqlldr oracle/oracle control=user.ctl external_table=generate_only

    eg:

     1、user.ctl(语法 可以使用EM DBCONSOLE生成) :

     load

     infile ‘D:\oracle_file\data.txt’

     badfile ‘D:\oracle_file\data.bad’

     discardfile ‘D:\oracle_file\data.dis’

     errors=50

     append

     into table user_data

     fields terminated by ‘,’ optionally enclosed by ‘”‘

     trailing nullcols

     (user_name varchar2(20),

       user_id number)

    2、sqlldr oracle/oracle control=user.ctl external_table=generate_only log=user_data.log

         你会在日志中发现：

       
文件需要 CREATE DIRECTORY 语句
————————————————————————
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS ‘D:\oracle_file\’

用于外部表的 CREATE TABLE 语句:
————————————————————————
CREATE TABLE “SYS_SQLLDR_X_EXT_USER_DATA”
(
  “USER_NAME” VARCHAR2(20),
  “USER_ID” 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′:’data.bad’
    DISCARDFILE ‘SYS_SQLLDR_XT_TMPDIR_00000′:’data.dis’
    LOGFILE ‘user_data.log_xt’
    READSIZE 1048576
    FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      “USER_NAME” CHAR(255)
        TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘,
      “USER_ID” CHAR(255)
        TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
    )
  )
  location
  (
    ‘data.txt’
  )
)REJECT LIMIT UNLIMITED

4、举例：

SQL> create or replace directory user_data_dir as ‘D:\oracle_file\’;

目录已创建。

SQL> CREATE TABLE user_data1
  2  (
  3    “USER_NAME” VARCHAR2(20),
  4    “USER_ID” NUMBER
  5  )
  6  ORGANIZATION external
  7  (
  8    TYPE oracle_loader
  9    DEFAULT DIRECTORY user_data_dir
 10    ACCESS PARAMETERS
 11    (
 12      RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
 13      BADFILE ‘data.bad’
 14      DISCARDFILE ‘data.dis’
 15      LOGFILE ‘user_data.log’
 16      READSIZE 1048576
 17      FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘ LDRTRIM
 18      MISSING FIELD VALUES ARE NULL
 19      REJECT ROWS WITH ALL NULL FIELDS
 20      (
 21        “USER_NAME” CHAR(255)
 22          TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘,
 23        “USER_ID” CHAR(255)
 24          TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
 25      )
 26    )
 27    location
 28    (
 29      ‘data.txt’
 30    )
 31  )REJECT LIMIT UNLIMITED
 32  /

表已创建。

SQL> select * from user_data1;

USER_NAME               USER_ID
——————– ———-
adfadf                        1
adfa1                         2
hdhgh                         3
a4f                           4
adf
                              5

已选择6行。

data.txt:

adfadf,1
adfa1,2
hdhgh,3
a4f,4
,
adf,
,5