ORACLE 外部表

1.前期准备

• 1.1 数据文件

以，或者其他方式分隔的数据文件

[oracle@host01]$ zcat ceshi.dat.gz 
A,AA,1,11,AAA
B,BB,2,22,BBB
C,CC,3,33,CCC
D,DD,4,44,DDD
E,EE,5,55,EEE
F,FF,6,66,FFF
G,GG,7,77,ggg

• 1.2 解压缩脚本

[oracle@host01]$ cat uncompress.sh 
/bin/gunzip -c $1

2.编辑sqlldr控制文件

• 2.1 按数据文件的内容建表

create table sh.ceshi(sname varchar2(2000),cname varchar2(2000),sno varchar2(2000),cno varchar2(2000));

• 2.2 编辑控制文件

[oracle@host01]$ cat ceshi.ctl
LOAD DATA
INFILE '/home/oracle/scripts/ceshi.dat.gz'
BADFILE '/home/oracle/scripts/ceshi.bad'
DISCARDFILE '/home/oracle/scripts/ceshi.dsc'
APPEND
INTO TABLE sh.prod_master
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(sname, cname, sno, cno)

3.生成外部表语句

注：这里要使用sqlldr的一个参数:generate_only:sqlldr并不执行加载,而是生成创建外部表的sql和处理数据的sql，并保存在log文件中,用户可
以修改后拿到sqlplus中执行

• 3.1 使用sqlldr生成log文件

[oracle@host01 Skillset3]$ sqlldr sh/sh control=ceshi.ctl log=external.log external_table=generate_only
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 19 22:38:58 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Path used:      External Table

• 3.2 查看log文件

[oracle@host01 Skillset3]$ cat  external.log 

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 19 22:38:58 2022

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   ceshi.ctl
Data File:      /home/oracle/scripts/ceshi.dat.gz
  Bad File:     /home/oracle/scripts/ceshi.bad
  Discard File: /home/oracle/scripts/ceshi.dsc 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table SH.CESHI, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SNAME                               FIRST     *   ,  O(") CHARACTER            
CNAME                                NEXT     *   ,  O(") CHARACTER            
SNO                                  NEXT     *   ,  O(") CHARACTER            
CNO                                  NEXT     *   ,  O(") CHARACTER            



CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00002 AS '/home/oracle/scripts/
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/scripts/'


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_CESHI" 
(
  "SNAME" VARCHAR2(2000),
  "CNAME" VARCHAR2(2000),
  "SNO" VARCHAR2(2000),
  "CNO" VARCHAR2(2000)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ceshi.bad'
    DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ceshi.dsc'
    LOGFILE 'SYS_SQLLDR_XT_TMPDIR_00002':'external.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM 
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "SNAME" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "CNAME" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "SNO" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "CNO" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location 
  (
    'ceshi.dat.gz'
  )
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO SH.CESHI 
(
  SNAME,
  CNAME,
  SNO,
  CNO
)
SELECT 
  "SNAME",
  "CNAME",
  "SNO",
  "CNO"
FROM "SYS_SQLLDR_X_EXT_CESHI"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_CESHI"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00002
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000



Run began on Mon Sep 19 22:38:58 2022
Run ended on Mon Sep 19 22:38:58 2022

Elapsed time was:     00:00:00.16
CPU time was:         00:00:00.00

• 3.3 修改log文件

[oracle@host01 Skillset3]$ cat external.log 
CREATE  TABLE sh.ceshi 
(
  "SNAME" VARCHAR2(2000),
  "CNAME" VARCHAR2(2000),
  "SNO" VARCHAR2(2000),
  "CNO" VARCHAR2(2000)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00002
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
    PREPROCESSOR 'SYS_SQLLDR_XT_TMPDIR_00002':'uncompress.sh'
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00002':'ceshi.bad'
    DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00002':'ceshi.dsc'
    LOGFILE 'SYS_SQLLDR_XT_TMPDIR_00002':'external.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM 
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "SNAME" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "CNAME" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "SNO" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "CNO" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location 
  (
    'ceshi.dat.gz'
  )
)REJECT LIMIT UNLIMITED;

4.建表

• 4.1 创建log文件中的目录,并授权

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00002 AS '/home/oracle/scripts/';
grant read,write,execute on directory SYS_SQLLDR_XT_TMPDIR_00002 to public;

• 4.2 删除之前创建的表

SH@PROD4 >drop table sh.ceshi;

Table dropped.

• 4.3 执行log文件

SH@PROD4 >@external.log

Table created.

5. 验证

SH@PROD4 >set pages 200
SH@PROD4 >col sname for a20
SH@PROD4 >col cname for a20
SH@PROD4 >col sno for a20
SH@PROD4 >col cno for a20
SH@PROD4 >select * from ceshi;

SNAME		     CNAME		  SNO		       CNO
-------------------- -------------------- -------------------- --------------------
A		     AA 		  1		       11
B		     BB 		  2		       22
C		     CC 		  3		       33
D		     DD 		  4		       44
E		     EE 		  5		       55
F		     FF 		  6		       66
G		     GG 		  7		       77

7 rows selected.

外部表创建成功(注意，外部表只可以做select操作)