目的：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

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

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

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

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