sql loader 语法(转)
来源:互联网 发布:路由器网络限速解除 编辑:程序博客网 时间:2024/05/01 02:29
转自annicybc空间:)
在北信的时候学习sqlloader的笔记,留着查询用。现在基本忘记上大学的日子了。
- 控制根据数据不同插入不同的表(条件装载);
- FIELDS TERMINATED BY x’09′ (制表符)whitespace(空格)等不同的数据分隔符号
- 装载定长数据;
- 装载变长数据;
- 装载嵌套数据;
- 在sqlldr中使用函数
- 如何装载日期类型纪录
- 装载分区表;
- TRAILING NULLCOLS
- 使用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
seethe control file just TRAILING NULLCOls the purpose of our sqlldr isexplicit. we want to load entire_line into table but it doesn’t existin BEGINDATA segements .
so oracle provide a flag TRAILING NULLCOLSIF U DON’T USE IT ALL ROWS WILL BE DISCARD INTO DISCARD FILE IF Uappoint 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
=================================================
-------------------------------------------------------------------------------------
常见错误:
Cause
No terminator was found after a terminated and enclosed field.
Action
Supply the character after the end of the field.
ORA-02358 Zoned Decimal conversion error
ORA-02359 Field in data file exceeded maximum specified length
ORA-02360 Column not found before end of logical record (use TRAILING NULLCOLS)
ORA-02361 Initial enclosing character not found
ORA-02362 Logical record ended - second enclosing character not present
ORA-02364 Record string: Discarded - failed all WHEN clauses
ORA-02365 Index string was made Index Unusable due to
ORA-02366 The following index(es) on table string were processed:
ORA-02367 Index string was loaded.
ORA-02368 Record string: Discarded - all columns null.
http://oratip.com/ORA-02363.html
- sql loader 语法(转)
- ORACLE SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- ORACLE SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- Oracle SQL Loader的详细语法
- SQL*Loader使用指南(转)
- 即时同步两个表的实例
- 评人月神话-兼谈项目管理
- 配置tomcat数据源的误区
- Tomcat5.5 部署 deferredFileOutputStream异常 commons-io.jar commons-fileupload jar
- 知识词典 → 谜语
- sql loader 语法(转)
- 用Visual C#实现P2P应用程序
- 遍历目录进行文件处理
- WAP
- 知识词典 »网站地图
- PC系统基础知识及软硬简单故障排解大全(4)
- WAP
- VB双进程保护
- Collection判断两个对象是否相等