sql loader难就难在要根据各种文本数据类型，编辑控制文件，用于告诉oracle如何读取从其他数据库导出的文本文件。

以下用例子说明。

测试环境，首先建表：

CREATE TABLE TB_FACT_CUST_FUND
 ("CUST_NO"        VARCHAR(10)    DEFAULT '' NOT NULL  ,
  "CURRENCY_TYPE"  CHARACTER(2)  DEFAULT ''  NOT NULL  ,
  "OCCUR_DATE"     CHARACTER(8)    DEFAULT ''  NOT NULL,
  "BRANCH_CODE"    VARCHAR(4)     DEFAULT ''  NOT NULL ,
  "CASH_BALANCE"   DECIMAL(19, 4)   DEFAULT 0 NOT NULL,
  "STOCK_ASSET"    DECIMAL(19, 4)   DEFAULT 0 NOT NULL,
  "TOTAL_ASSET"    DECIMAL(19, 4)   DEFAULT 0 NOT NULL
 )

假设导出的文本csv文件遇到如下格式，看看怎么处理。

案例1（标准格式，即字符类型用双引号括起来，所以字段用逗号分割），例如

"30303350","01","20100601","030",3265.5488,1827.4046,15092.9534
"30303450","01","20100601","030",88.7413,0.0000,88.7413

这里有2行数据。控制文件：

Load data
infile '/u01/fundtest.csv'
append into table EBS_BROKERAGE.TB_FACT_CUST_FUND
(
  CUST_NO  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  CURRENCY_TYPE TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  OCCUR_DATE TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  BRANCH_CODE  TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  CASH_BALANCE TERMINATED BY "," ,
  STOCK_ASSET TERMINATED BY "," ,
  TOTAL_ASSET TERMINATED BY ","
  )

TERMINATED BY "," 代表碰到逗号停止读取数据，即字段间用逗号分割。OPTIONALLY ENCLOSED BY '"'代表字段可能用双引号扩起来。注意，这里by后面是外层2个单引号，里面一个双引号，可能看不清。

案例2（非主流格式，我这次就碰到的恶心问题，字符类型用单引号括起来，所以字段用逗号分割）：例如：

'30303350','01','20100601','030',3265.5488,1827.4046,15092.9534
'30303450','01','20100601','030',88.7413,0.0000,88.7413

2行数据。控制文件：

Load data
infile '/u02/fundtest.csv'
append into table EBS_BROKERAGE.TB_FACT_CUST_FUND
(
  CUST_NO  TERMINATED BY "," OPTIONALLY ENCLOSED BY '\'',
  CURRENCY_TYPE TERMINATED BY "," OPTIONALLY ENCLOSED BY '\'',
  OCCUR_DATE TERMINATED BY "," OPTIONALLY ENCLOSED BY '\'',
  BRANCH_CODE  TERMINATED BY "," OPTIONALLY ENCLOSED BY '\'',
  CASH_BALANCE TERMINATED BY "," ,
  STOCK_ASSET TERMINATED BY "," ,
  TOTAL_ASSET TERMINATED BY ","
  )

单引号在oracle里比较特殊，必须使用\代表原意输出。

案例3：如果csv中所有同意字段的记录都一样长度，那就比较好处理，可以在控制文件中使用position字段，告诉oracle从每行记录的第几个字符开始读入，到哪个字符终止。例如：

append into table EBS_BROKERAGE.TB_FACT_CUST_CLASS
(
  CUST_NO  POSITION(2:9),  #表示cust_no字段从行记录第二个字符开始记录到第九个字段结束，以下类推。
  PERIOD_ID  POSITION(13:20),
  CLASS_ID  POSITION(24:29),
  BRANCH_CODE  POSITION(33:35),
  CLASS_COMMENT  POSITION(39:39)
)

sqlldr userid=EBS_BROKERAGE/***** CONTROL=/u01/1.ctl LOG=/u01/fundtest.log DIRECT=TRUE

PARALLEL=TRUE  load=3（样例备忘）