sqlldr语法

在北信的时候学习sqlloader的笔记，留着查询用。现在基本忘记上大学的日子了。

1. 控制根据数据不同插入不同的表（条件装载）;
2. FIELDS TERMINATED BY x’09′ （制表符）whitespace（空格）等不同的数据分隔符号
3. 装载定长数据;
4. 装载变长数据;
5. 装载嵌套数据;
6. 在sqlldr中使用函数
7. 如何装载日期类型纪录
8. 装载分区表;
9. TRAILING NULLCOLS
10. 使用filler 跳过 在导入数据文本中不想进行导入的列

控制根据数据不同插入不同的表
包括文件:日志文件;控制文件;坏记录文件;废弃记录文件(when)
控制文件:
load data
1说明输入文件 infile后根文件名.默认扩展名为’.dat’
infile=’accounts’
用单引号’ 括起文件名accounts.dat
2处理选项discardfile badfile
load data
infile ‘accounts’ discardfile mtidsc.rec badfile mthad.rec
3目标对象—–表
slqloader的用户必要有insert的权限.into table 开头
into table account_trans
when day between ‘01′ and ‘31′
into table account_nbr
when account_type between ‘aa’ and ‘zz’  这样可以根据条件插入不同的表
4目标对象—-分区表或者某个分区
into table sale partition(east_data)…..
如果一次装在所有分区,可以用目标对象—-表的方法进行处理 into table sale….
5记录生成模式—Insert,Replace,Append
Insert–缺省模式,装在之前,table必须为空表;;
Replace –先删除所有记录,然后装在满足when条件的行;(需要delete的权限)
Append–表中原有记录保存,加入新的行.;;

处理定长记录
load data
infile ‘account.dat’
into table count_trans append
when year=’1990′
(account_nbr position(01:10) character,
day position(11:12) character,
month position(13:14) character,
transaction_code position(15:16) character,
credit_amount position(17:30) character)
into table count replace
when year>’1990′
(account_nbr position(01:10) character,
day position(11:12) character,
month position(13:14) character,
transaction_code position(15:16) character,
credit_amount position(17:30) character)

处理变长记录(从access到oracle)
分隔符:概念.在一行中将一项信息与另一项信息分离开的一个字符标记.
load data
infile ‘customer.dat’
into table aa append
(customer_id char terminated by ‘ ‘,
status char terminated by ‘ ‘,
dsc_class char enclosed by ‘ ‘,
source char terminated by whitespace)
1>单引号分割.末尾的信息项不已逗号结束,用关键字whitespace

FIELDS TERMINATED BY x’09′ （制表符）

LOAD DATA
INFILE *
INTO TABLE DEPT1
REPLACE
FIELDS TERMINATED BY X’09′
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
by x’09′遇见一个制表符.它将输出一个直 也就是在制表符之间的数据
SVRMGR> host sqlldr scott/scott control=c:control.txt
SQL*Loader: Release 8.1.7.0.0 - Production on 星期六 8月 24 21:04:26 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数1
SVRMGR> select *from dept1;
DEPTNO DNAME LOC
———- ————– ————-
10 Sales
已选择 1 行。

使用filler 跳过 在导入数据文本中不想进行导入的列

demo
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED date ‘dd/mm/yyyy’
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001

u can see upper functions
sqlldr is a tool that can parse input variables and form its insert sql using bind values.
like normal controlfile
FIELDS TERMINATED BY ‘,’
(DEPTNO,
DNAME ,
LOC ,
LAST_UPDATED date
)
oracle’s sqlldr change it equal insert into table values(:deptno,:dname,:loc,:last_updated);
when load data sqlldr parse each record row and bind variables
once parse else execute!

so like
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED date ‘dd/mm/yyyy’
)
sql : insert into table values(:deptno,upper(name),upper(:loc),:last_updated )
the ” LAST_UPDATED date ‘dd/mm/yyyy’ ” inside date ‘dd/mm/yyyy’ is only the variable ’s datatype not functions
u can aslo use function to_date() then it deference

SVRMGR> host sqlldr scott/scott control=control.txt
SQL*Loader: Release 8.1.7.0.0 - Production on 星期日 8月 25 00:46:04 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数4
SVRMGR> select *from dept1;
DEPTNO DNAME LOC LAST_UPDA
———- ————– ————- ———
10 SALES VIRGINIA 01-5月 -0
20 ACCOUNTING VIRGINIA 21-6月 -9
30 CONSULTING VIRGINIA 05-1月 -0
40 FINANCE VIRGINIA 15-3月 -0
已选择4行。

TRAILING NULLCOLS

my compute just down i lose some article yet not been submit
i am so lazy not want to repeat just go on topic
about trailing nullcols
see the control file just TRAILING NULLCOls the purpose of our sqlldr is explicit. we want to load entire_line into table but it doesn’t exist in BEGINDATA segements .
so oracle provide a flag TRAILING NULLCOLS IF U DON’T USE IT ALL ROWS WILL BE DISCARD INTO DISCARD FILE IF U appoint it u shoud try what i said in no using TRAILING NULLCOLS mode

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED date ‘dd/mm/yyyy’,
ENTIRE_LINE “:deptno||:dname||:loc||:last_updated”
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001

let’s goon
TRAILING NULLCOLS for u to appoint the last column a value “NULL” so in begindata it form a noexist column with value null
do it
SVRMGR> select *from dept1;
DEPTNO DNAME LOC LAST_UPDA ENTIRE_LINE
———- ————– ————- ——— ——————————
10 SALES VIRGINIA 01-5月 -0 10SalesVirginia1/5/2000
40 FINANCE VIRGINIA 15-3月 -0 40FinanceVirginia15/3/2001
20 ACCOUNTING VIRGINIA 21-6月 -9 20AccountingVirginia21/6/1999
30 CONSULTING VIRGINIA 05-1月 -0 30ConsultingVirginia5/1/2000
已选择4行。
SVRMGR>

sqlldr with function is powerful magic

power function CASE When…..END relation discard file
just provide a controlf file
lazy to do it
———————————————–
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED “case when length(:last_updated) <= 10
then to_date(:last_updated,’dd/mm/yyyy’)
else to_date(:last_updated,’dd/mm/yyyy hh24:mi:ss’)
end”
)
BEGINDATA
10,Sales,Virginia,1/5/2000 12:03:03
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000 01:23:00
40,Finance,Virginia,15/3/2001

sometimes customer give our data.txt date format are too different so we have ablility to create own functions
BEGINDATA
10,Sales,Virginia,01-april-2001
20,Accounting,Virginia,13/04/2001
30,Consulting,Virginia,14/04/2001 12:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not a date

i copy a good fucniton from TOM
create or replace
function my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( ‘dd-mon-yyyy’, ‘dd-month-yyyy’,
‘dd/mm/yyyy’,
‘dd/mm/yyyy hh24:mi:ss’ );
l_return date;
begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;
if ( l_return is null )
then
l_return :=
new_time( to_date(’01011970′,’ddmmyyyy’) + 1/24/60/60 *
p_string, ‘GMT’, ‘EST’ );
end if;
return l_return;
end;
/

use of it if other format it would in bad file we kan reload it

使用sqlldr 应该注意的问题

1 不能选择使用哪个回滚段
装载的时候 使用replace 来slqldr时候 在装载之前,它会发出delete命令 这样将产生大量的回滚. 为了实现这个操作 你可能想要指定sqlldr使用一个特定的回滚段

你必须保证任何一个回滚段有足够的长度来容纳delete 或者使用truncate 选项 由于insert并没有产生过多的回滚,因为它只写 rowid into redo 理解没有问题吧??

2truncate选项 为truncate table t reuse storage

SQLLDR默认输入流为数据类型为CHAR 长度为 255 所以当 begindata section里面的 输入流 长于255 的时候会报错的
应该是 Record N Rejected Eorr for columnn
field in data file exceed max length

sqlldr希望你输入小于255字节或更少字节的数据,而获得的比这要多,解决的方法是在控制文件中只是单纯地使用 char(N) n是可以包括你输入列地最大长度

比方说load long类型 地时候 可以 输入 char(10000) ^_^

就到这里 大家如果发现有什么bug 请贴出来 thx

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
( DEPTNO,
FILLER_1 FILLER, //see it filler
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,”Virginia,USA”
当装载数据时候 需要在输入记录中跳过各种不同的列是非常普通的

例如你想 装入 1.3.5列 跳过2.4列 可以利用filler
它可以让我们在数据流里面指定一个列 不把他放到数据库中
SVRMGR> host sqlldr scott/scott control=c:control.txt
SQL*Loader: Release 8.1.7.0.0 - Production on 星期六 8月 24 21:16:52 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数1
SVRMGR> select *from dept1;
DEPTNO DNAME LOC
———- ————– ————-
20 Accounting Virginia,USA

在sqlldr中使用函数 解析sqlldr本质 important