目的：sqlldr中使用條件裝載及作為外部表訪問。

1，sqlldr中使用條件裝載：

[oracle@bocnet95 mh]$ cat netval.sql
create table netval
(
fncode          VARCHAR2(6) not null,
fndate          date,
netval          NUMBER(7,4),
enddate         date,
addupbns        NUMBER(7,4),
addupnav        NUMBER(7,4),
fundincomeunit NUMBER(7,4),
fundincomeratio NUMBER(7,4),
dayincomeratio NUMBER(7,4)
)
;

[oracle@bocnet95 mh]$ cat netval.dat
|000012|20110930|10.911|20110930|0|0|0|0|0|
|000031|20110930|1.134|20110930|0|0|0|0|0|
|000021|20110930|1.599|20110930|0|0|0|0|0|
|000001|20110930|1.003|20110930|0|0|0|0|0|
|000011|20110930|10.911|20110930|0|0|0|0|0|
|002021|20110930|1.042|20110930|0|0|0|0|0|
|002011|20110930|1.635|20110930|0|0|0|0|0|
|002031|20110930|2.252|20110930|0|0|0|0|0|
|002002|20110930|1.219|20110930|0|0|0|0|0|
|000061|20110930|.764|20110930|0|0|0|0|0|

[oracle@bocnet95 mh]$ cat netval.ctl
LOAD DATA
INFILE 'netval.dat'
REPLACE
INTO TABLE netval
WHEN FNCODE= '000021'
FIELDS TERMINATED BY '|'
(
col filler,
FNCODE,
FNDATE date "YYYYMMDD",
NETVAL,
ENDDATE date "YYYYMMDD",
ADDUPBNS,
ADDUPNAV,
FUNDINCOMEUNIT,
FUNDINCOMERATIO,
DAYINCOMERATIO
)

[oracle@bocnet95 mh]$ sqlldr mh/mh control=netval.ctl
裝載後可以查詢到符合條件的一條記錄。

2，sqlldr中作為外部表訪問。
2.1使用sqlldr生成建立外部表的sql：
[oracle@bocnet95 mh]$ sqlldr mh/mh control=netval.ctl external_table=generate_only
SQL*Loader: Release 10.2.0.1.0 - Production on Thu May 3 10:56:21 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
[oracle@bocnet95 mh]$ ls
netval.dat
netval.sql
netval.ctl
netval.log

[oracle@bocnet95 mh]$ cat netval.log
SQL*Loader: Release 10.2.0.1.0 - Production on Thu May 3 10:56:21 2012

Control File:   netval.ctl
Data File:      netval.dat
Bad File:     netval.bad
Discard File: none specified

(Allow all discards)

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

Table NETVAL, loaded when FNCODE = 0X303030303231(character '000021')
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL                                 FIRST     *   |       CHARACTER
(FILLER FIELD)
FNCODE                               NEXT     *   |       CHARACTER
FNDATE                               NEXT     *   |       DATE YYYYMMDD
NETVAL                               NEXT     *   |       CHARACTER
ENDDATE                              NEXT     *   |       DATE YYYYMMDD
ADDUPBNS                             NEXT     *   |       CHARACTER
ADDUPNAV                             NEXT     *   |       CHARACTER
FUNDINCOMEUNIT                       NEXT     *   |       CHARACTER
FUNDINCOMERATIO                      NEXT     *   |       CHARACTER
DAYINCOMERATIO                       NEXT     *   |       CHARACTER

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

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_NETVAL"
(
"FNCODE" VARCHAR2(6),
"FNDATE" DATE,
"NETVAL" NUMBER(7,4),
"ENDDATE" DATE,
"ADDUPBNS" NUMBER(7,4),
"ADDUPNAV" NUMBER(7,4),
"FUNDINCOMEUNIT" NUMBER(7,4),
"FUNDINCOMERATIO" NUMBER(7,4),
"DAYINCOMERATIO" NUMBER(7,4)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
    RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
    LOAD WHEN ("FNCODE" = "000021")
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'netval.bad'
    LOGFILE 'netval.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "|" LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "COL" CHAR(255)
        TERMINATED BY "|",
      "FNCODE" CHAR(255)
        TERMINATED BY "|",
      "FNDATE" CHAR(255)
        TERMINATED BY "|"
        DATE_FORMAT DATE MASK "YYYYMMDD",
      "NETVAL" CHAR(255)
        TERMINATED BY "|",
      "ENDDATE" CHAR(255)
        TERMINATED BY "|"
        DATE_FORMAT DATE MASK "YYYYMMDD",
      "ADDUPBNS" CHAR(255)
        TERMINATED BY "|",
      "ADDUPNAV" CHAR(255)
        TERMINATED BY "|",
      "FUNDINCOMEUNIT" CHAR(255)
        TERMINATED BY "|",
      "FUNDINCOMERATIO" CHAR(255)
        TERMINATED BY "|",
      "DAYINCOMERATIO" CHAR(255)
        TERMINATED BY "|"
    )
)
location
(
    'netval.dat'
)
)REJECT LIMIT UNLIMITED

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO NETVAL
(
FNCODE,
FNDATE,
NETVAL,
ENDDATE,
ADDUPBNS,
ADDUPNAV,
FUNDINCOMEUNIT,
FUNDINCOMERATIO,
DAYINCOMERATIO
)
SELECT
"FNCODE",
"FNDATE",
"NETVAL",
"ENDDATE",
"ADDUPBNS",
"ADDUPNAV",
"FUNDINCOMEUNIT",
"FUNDINCOMERATIO",
"DAYINCOMERATIO"
FROM "SYS_SQLLDR_X_EXT_NETVAL"

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

Run began on Thu May 03 10:56:21 2012
Run ended on Thu May 03 10:56:21 2012

Elapsed time was: 00:00:00.07
CPU time was: 00:00:00.02

2.2 建立外部表、驗證：
[oracle@bocnet95 mh]$ sqlplus mh/mh

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 3 10:59:11 2012

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/mh'
2 ;

Directory created.

SQL> CREATE TABLE "SYS_SQLLDR_X_EXT_NETVAL"
2 (
3    "FNCODE" VARCHAR2(6),
4    "FNDATE" DATE,
5    "NETVAL" NUMBER(7,4),
6    "ENDDATE" DATE,
7    "ADDUPBNS" NUMBER(7,4),
8    "ADDUPNAV" NUMBER(7,4),
9    "FUNDINCOMEUNIT" NUMBER(7,4),
10    "FUNDINCOMERATIO" NUMBER(7,4),
11    "DAYINCOMERATIO" NUMBER(7,4)
12 )
13 ORGANIZATION external
14 (
15    TYPE oracle_loader
16    DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
17    ACCESS PARAMETERS
18    (
19      RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
20      LOAD WHEN ("FNCODE" = "000021")
21      BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'netval.bad'
22      LOGFILE 'netval.log_xt'
23      READSIZE 1048576
24      FIELDS TERMINATED BY "|" LDRTRIM
25      REJECT ROWS WITH ALL NULL FIELDS
26      (
27        "COL" CHAR(255)
28          TERMINATED BY "|",
29        "FNCODE" CHAR(255)
30          TERMINATED BY "|",
31        "FNDATE" CHAR(255)
32          TERMINATED BY "|"
33          DATE_FORMAT DATE MASK "YYYYMMDD",
34        "NETVAL" CHAR(255)
35          TERMINATED BY "|",
36        "ENDDATE" CHAR(255)
37          TERMINATED BY "|"
38          DATE_FORMAT DATE MASK "YYYYMMDD",
39        "ADDUPBNS" CHAR(255)
40          TERMINATED BY "|",
41        "ADDUPNAV" CHAR(255)
42          TERMINATED BY "|",
43        "FUNDINCOMEUNIT" CHAR(255)
44          TERMINATED BY "|",
45        "FUNDINCOMERATIO" CHAR(255)
46          TERMINATED BY "|",
47        "DAYINCOMERATIO" CHAR(255)
48          TERMINATED BY "|"
49      )
50    )
51    location
52    (
53      'netval.dat'
54    )
55 )REJECT LIMIT UNLIMITED;

Table created.

SQL> SELECT
2    "FNCODE",
3    "FNDATE",
4    "NETVAL",
5    "ENDDATE",
6    "ADDUPBNS",
7    "ADDUPNAV",
8    "FUNDINCOMEUNIT",
9    "FUNDINCOMERATIO",
10    "DAYINCOMERATIO"
11 FROM "SYS_SQLLDR_X_EXT_NETVAL"
12 ;

FNCODE FNDATE                 NETVAL ENDDATE              ADDUPBNS   ADDUPNAV
------ ------------------ ---------- ------------------ ---------- ----------
FUNDINCOMEUNIT FUNDINCOMERATIO DAYINCOMERATIO
-------------- --------------- --------------
000021 30-SEP-11               1.599 30-SEP-11                   0          0
             0               0              0

sqlldr中使用條件裝載及作為外部表訪問

相關文章