实用复杂的sql loader案例
Oracle Database 12c: Administration Workshop P116:1
-- This is a sample control file
2 LOAD DATA
3 INFILE ’SAMPLE.DAT’
4 BADFILE ’sample.bad’
5 DISCARDFILE ’sample.dsc’
’
’
6 APPEND
7 INTO TABLE emp
8 WHEN (57) = ’.’
9 TRAILING NULLCOLS
10 (hiredate SYSDATE,
deptno POSITION(1:2) INTEGER EXTERNAL(3)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,’$99,999.99’)",
comm INTEGER EXTERNAL ENCLOSED BY ’(’ AND ’%’
":comm * 100"
)
原文件在此：
Today's Newly Hired Employees
DeptJob Manager MgrNoEmp NameEmpNoSalary/Commission
-------------------------------------------------------
20 SalesmanBlake 7698Shepard 8061$1,600.00 (3%)
Falstaff 8066$1,250.00 (5%)
Major 8064$1,250.00 (14%)
30 Clerk Scott 7788Conrad 8062$1,100.00
Ford 7369DeSilva 8063 $800.00
Manager King 7839Provo 8065$2,975.00
控制文件：
LOAD DATA
INFILE 'ulcase7.dat'
DISCARDFILE 'ulcase7.dis'
APPEND
INTO TABLE emp
WHEN (57)='.'
TRAILING NULLCOLS
(hiredate SYSDATE,
deptno POSITION(1:2)INTEGER EXTERNAL(3)
NULLIF deptno=BLANKS,
job POSITION(7:14) CHARTERMINATED BY WHITESPACE
NULLIF job=BLANKS"UPPER(:job)",
mgr POSITION(28:31)INTEGER EXTERNAL TERMINATED BY WHITESPACE
NULLIF mgr=BLANKS,
ename POSITION (34:41) CHARTERMINATED BY WHITESPACE
"UPPER(:ename)",
empno INTEGER EXTERNALTERMINATED BY WHITESPACE,
sal POSITION(51)CHARTERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
comm INTEGER EXTERNALENCLOSED BY '(' AND '%'
":comm * 100"
)
~
做实验的过程：
set termout off
rem host write sys$output "Building case 6 demonstration tables.Please wait"
drop table emp;
create table emp
(empno number(4) not null,
ename char(10),
job char(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2));
create unique index empix on emp(empno);
exit
$ sqlldr hr/oracle_4Ucontrol=ulcase7.ctldirect=y8061 SHEPARD SALESMAN 7698 17-NOV-20 1600 300 20
8066 FALSTAFF 17-NOV-20 1250 500
8064 MAJOR 17-NOV-20 1250 1400
8062 CONRAD CLERK 7788 17-NOV-20 1100 30
8063 DESILVA 7369 17-NOV-20 800
8065 PROVO MANAGER 7839 17-NOV-20 2975
$ sqlldr hr/oracle_4Ucontrol=ulcase7.ctlexternal_table=GENERATE_ONLYlog=ext.sql
SQL*Loader: Release 12.1.0.2.0 - Production on 星期二 11月 17 20:33:16 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
SQL*Loader-307: Warning: conflicting lengths 2 and 3 specified for column DEPTNO table EMP
Path used: External Table
-----------------------------------
CREATE TABLE ext1
(
"DEPTNO" NUMBER(2),
"JOB" VARCHAR2(255),
"MGR" NUMBER(4),
"ENAME" VARCHAR2(255),
"EMPNO" NUMBER(4),
"SAL" VARCHAR2(255),
"COMM" VARCHAR2(255)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY dir1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
LOAD WHEN ((57: 57) = ".")
BADFILE 'ulcase7.bad'
DISCARDFILE 'ulcase7.dis'
LOGFILE 'ext.log'
READSIZE 1048576
FIELDS LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" (1:2) INTEGER EXTERNAL(3)
NULLIF ("DEPTNO" = BLANKS),
"JOB" (7:14) CHAR(8)
NULLIF ("JOB" = BLANKS),
"MGR" (28:31) CHAR(4)
NULLIF ("MGR" = BLANKS),
"ENAME" (34:41) CHAR(8),
"EMPNO" CHAR(255)
TERMINATED BY WHITESPACE,
"SAL" (51) CHAR(255)
TERMINATED BY WHITESPACE,
"COMM" CHAR(255)
ENCLOSED BY "(" AND "%"
)
)
location
(
'ulcase7.dat'
)
)REJECT LIMIT UNLIMITED;
ccb.ctl:
LOAD DATA
characterset ZHS16GBK
INFILE 'ccb.dat'
DISCARDFILE 'ccb.dis'
append
INTO table t_bank_invoice
TRAILING NULLCOLS
(
account_number "4367421823250099999",
sub_number "00000",
transac_date position(1:8)DATE(8) "YYYYMMDD" TERMINATED BY ',',
transac_address position(10) charTERMINATED BY ',',
out_amount decimal externalTERMINATED BY ','nullif out_amount=blanks,
in_amount decimal externalTERMINATED BY ',' nullif in_amount=blanks,
balance decimal externalTERMINATED BY ',' nullif balance=blanks,
opposite_number charTERMINATED BY ',',
opposite_username charTERMINATED BY ',',
currency charTERMINATED BY ',',
transac_commentcharTERMINATED BY whitespace
)
账 号：4367421823250099999
开户机构：福建省
币 种： 人民币
发生日期,交易地点,支出,收入,账户余额,对方账号,对方户名,币种,摘要
20100209,008350183980040,150.00,,6756.67,,,人民币,消费
20100210,,,2765.90,9522.57,,,人民币,工资/奖金
20100216,,3.00,,9519.57,,,人民币,自定义
20100217,,4859.10,,4660.47,,,人民币,自定义
icbc.ctl:
LOAD DATA
characterset ZHS16GBK
INFILE 'icbc.dat'
DISCARDFILE 'icbc.dis'
append
INTO table t_bank_invoice
when (5) ='^'
FIELDS TERMINATED BY '^'
TRAILING NULLCOLS
(
account_number "1402029101000999999",
sub_number "00000",
transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
transac_address TERMINATED BY WHITESPACE
"substr(:transac_address,2)",
transac_comment TERMINATED BY WHITESPACE
"substr(:transac_comment,2)",
currency TERMINATED BY WHITESPACE
"substr(:currency,2)",
remit TERMINATED BY WHITESPACE
"substr(:remit,2)",
in_amount TERMINATED BY WHITESPACE
"TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
out_amount TERMINATED BY WHITESPACE
"TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
balance TERMINATED BY WHITESPACE
"TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
all_comment TERMINATED BY WHITESPACE
"substr(:all_comment,2)"
)
明细查询文件下载
卡号: 1402029101000999999 卡别名:
子账户序号: 子账户类别: 子账户别名:
^交易日期 ^交易场所 ^业务摘要^币种 ^钞/汇 ^收入金额 ^支出金额 ^余额
^2010-05-10 ^ ^工资 ^人民币 ^钞 ^564.94 ^ ^6,748.42
^2010-05-14 ^ ^工资 ^人民币 ^钞 ^5,989.54 ^ ^12,737.96
^2010-05-26 ^金山支行营业部 ^支取 ^人民币 ^钞 ^ ^12,700.00 ^37.96
^2010-06-12 ^ ^工资 ^人民币 ^钞 ^5,811.04 ^ ^5,849.00
^2010-06-21 ^ ^息 ^人民币 ^钞 ^5.03 ^ ^5,854.03
^2010-07-01 ^金山支行营业部 ^支取 ^人民币 ^钞 ^ ^5,854.00 ^0.03
#人民币合计 ^163,952.85 ^170,134.00
页:
[1]