简而言之, fix告诉每次取一行的范围, 然后,通过看这一行相应的分隔符去分开对应字段中的数据。
而var的意思是,数据前多少位代表后面多少bytes算成一行,然后再在这一行中做分割。
举例:
1. fix的例子:
- [oracle@rac1 sqlldr]$ cat ctl1.txt
-
load data
-
infile 'example1.dat' "fix 8"
-
truncate into table t2
-
fields terminated by ',' optionally enclosed by '"'
-
(name1 ,
-
name2 )
-
[oracle@rac1 sqlldr]$ sqlldr milo/milo control=ctl1.txt
-
-
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Jan 18 17:09:20 2012
-
-
Copyright (c) 1982, 2007, Oracle. All rights reserved.
-
-
Commit point reached - logical record count 6
-
SQL*Loader-501: Unable to read file (example1.dat)
-
SQL*Loader-566: partial record found at end of datafile ===》 因为,发现有不符合规定的记录,例如取到的数据没有找到分隔符。
-
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
结果:
- SQL> select * from t2;
-
-
NAME1 NAME2
-
----- -------
-
001 c
-
d 0002
-
fghi 00
-
03 mn
分析:
数据如下:
- [oracle@rac1 sqlldr]$ cat example1.dat
-
001, cd, 0002,fghi,
-
0003,mn,1, "pqrs",abc
-
0005,uvwx,
===》 因为是"fix 8",每次取8个bytes作为一行
=====》 1. 取到“001, c” , 然后以","作为字段的分割,那么,结果插入的就是"001","c",开头的空格会被去掉。
=====》 2. 取到"d, 0002," , 然后以","作为字段分割, 那么,结果插入的就是"d","0002",相当于这一行分割了3个字段,因为有两个","。
"fghi,\n00" ====>
"03,mn,1," ====>
" "pqrs"," ====> 扫到这的时候,就有问题了。分割符后没有数据,之内能插入一个字段的数据。
所以报错SQL*Loader-566: partial record found at end of datafile。
- SQL*Loader-00566: partial record found at end of datafile
-
-
Cause:
-
An incomplete record was found at the end of file.
-
Action:
-
Make sure the last record in the datafile is complete and has the correct terminating character(s).
2. var的例子1:
var其实就是每次开头告诉sqlldr每行变化取多少位,相当于每次都指定个fix多少(由前面的数据决定位数)的值。这也就是变长的做法。
- 009hello,cd,010world,im,
-
-
012my,name is,
===》 例如这个是"var 3",也就是告诉sqlldr前3位数字是fix多少的参数。
- [oracle@rac1 sqlldr]$ cat ctl.txt
-
load data
-
infile 'example.dat' "var 3"
-
truncate into table t1
-
fields terminated by ',' -- optionally enclosed by '"'
-
(name1 char(5),
-
name2 char(7))
-
-
-
[oracle@rac1 sqlldr]$ cat example.dat
-
009hello,cd1
-
010world,im,
-
012my,name is,
在这种情况会报错:
- [oracle@rac1 sqlldr]$ sqlldr milo/milo control=ctl.txt
-
-
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Jan 18 17:40:43 2012
-
-
Copyright (c) 1982, 2007, Oracle. All rights reserved.
-
-
SQL*Loader-501: Unable to read file (example.dat)
-
SQL*Loader-563: bad length for VAR record
-
SQL*Loader-509: System error: Error 0
-
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
第一次,根据前面的"009"确认要在之后去9bytes,取到”
hello,cd1“;
第二次,确认去多少行的时候,通过"\n00"发现有非数字数据,因此,确认不了后面的取多少位。因此报错。
报错563里也说明得很清楚:包含有非数字的数据。
- SQL*Loader-00563: bad length for VAR record
-
-
Cause:
-
The length protion of a VAR record contains non-numeric data.
-
Action:
-
Fix the data in the file.
3. var的例子2:
- [oracle@rac1 sqlldr]$ cat example.dat
-
009hello,cd1010world,im,
-
012my,name is,
-
-
[oracle@rac1 sqlldr]$ cat ctl.txt
-
load data
-
infile 'example.dat' "var 3"
-
truncate into table t1
-
fields terminated by ',' -- optionally enclosed by '"'
-
(name1 char(5),
-
name2 char(7))
- [oracle@rac1 sqlldr]$ sqlldr milo/milo control=ctl.txt
-
-
SQL*Loader: Release 10.2.0.4.0 - Production on Wed Jan 18 17:54:25 2012
-
-
Copyright (c) 1982, 2007, Oracle. All rights reserved.
-
-
Commit point reached - logical record count 3
结果如下:
- SQL> select *from t1;
-
-
NAME1 NAME2
-
----- -------
-
hello cd1
-
world im
-
my name is
分析:
====> 第一次取数字,取到"009",也就是取到009的后面9位。取到"hello,cd1",插入"hello","cd1"
====> 第二次取数字,取到"010",也就是取到010的后面10位。取到"world,im,\n",插入数据"world","im",换行也被去掉。
====> 第三次取数字,取到"012",也就是取到012的后面12位。取到"my,name is,\n",插入数据"my","name is",后面的第三个字段内容忽略。
阅读(5671) | 评论(1) | 转发(1) |