ORACLE 外部表

[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
复制

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

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

[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)
复制

[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
复制

[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
复制

[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;
复制

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

SH@PROD4 >drop table sh.ceshi;

Table dropped.
复制

SH@PROD4 >@external.log
Table created.
复制

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.
复制