下面的是关于sql*loader 的使用的一点总结 有些是来自itpub上的一些网友的总结

　　大部分是oracle专家高级编程上的实例 只是我实践以后写的结果

　　sqlldr userid=lgone/tiger control=a.ctl

　　LOAD DATA

　　INFILE 't.dat'

　　// 要导入的文件

　　// INFILE 'tt.dat'

　　// 导入多个文件

　　// INFILE *

　　// 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容

　　INTO TABLE table_name

　　// 指定装入的表

　　// into table t_name partition (p_1)

　　分区的载入

　　BADFILE 'c:\bad.txt'

　　// 指定坏文件地址

　　*************

　　以下是4种装入表的方式

　　APPEND

　　// 原先的表有数据 就加在后面

　　// INSERT

　　// 装载空表 如果原先的表有数据 sqlloader会停止

　　默认值

　　// REPLACE

　　// 原先的表有数据 原先的数据会全部删除

　　// TRUNCATE

　　// 指定的内容和replace的相同 会用truncate语句删除现存数据

　　SKIP 5

　　可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据

　　*************

　　指定的TERMINATED可以在表的开头 也可在表的内部字段部分

　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

　　// 装载这种数据: 10,lg,"""lg""","lg,lg"

　　// 在表中结果: 10

　　lg

　　"lg"

　　lg,lg

　　// TERMINATED BY X '09'

　　// 以十六进制格式 '09' 表示的

　　// TERMINATED BY WRITESPACE

　　// 装载这种数据: 10 lg lg

　　TRAILING NULLCOLS

　　*************

　　表的字段没有对应的值时允许为空

　　*************

　　下面是表的字段

　　(

　　col_1 , col_2 ,col_filler FILLER // FILLER 关键字 此列的数值不会被装载

　　// 如: lg,lg,not

　　结果 lg

　　lg

　　)

　　// 当没声明FIELDS TERMINATED BY ',' 时

　　// (

　　//

　　col_1 [interger external] TERMINATED BY ',' ,

　　//

　　col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,

　　//

　　col_3 [char]

　　TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'

　　// )

　　// 当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据

　　// (

　　//

　　col_1 position(1:2),

　　//

　　col_2 position(3:10),

　　//

　　col_3 position(*:16),

　　// 这个字段的开始位置在前一字段的结束位置

　　//

　　col_4 position(1:16),

　　//

　　col_5 position(3:10) char(8)

　　// 指定字段的类型

　　// )

　　BEGINDATA

　　// 对应开始的 INFILE *

　　要导入的内容就在control文件里

　　10,Sql,what

　　20,lg,show

　　=====================================================================================

　　////////////

　　注意begindata后的数值前面不能有空格

　　1

　　***** 普通装载

　　LOAD DATA

　　INFILE *

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

　　(DEPTNO,

　　DNAME,

　　LOC

　　)

　　BEGINDATA

　　10,Sales,"""USA"""

　　20,Accounting,"Virginia,USA"

　　30,Consulting,Virginia

　　40,Finance,Virginia

　　50,"Finance","",Virginia

　　// loc 列将为空

　　60,"Finance",,Virginia

　　// loc 列将为空

　　2

　　***** FIELDS TERMINATED BY WHITESPACE 和

　　FIELDS TERMINATED BY x'09' 的情况

　　LOAD DATA

　　INFILE *

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY WHITESPACE

　　-- FIELDS TERMINATED BY x'09'

　　(DEPTNO,

　　DNAME,

　　LOC

　　)

　　BEGINDATA

　　10

　　Sales

　　Virginia

　　3

　　***** 指定不装载那一列

　　还可用 POSTION(x:y) 来分隔数据

　　LOAD DATA

　　INFILE *

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

　　( DEPTNO,

　　FILLER_1 FILLER,

　　// 下面的 "Something Not To Be Loaded" 将不会被装载

　　DNAME,

　　LOC

　　)

　　BEGINDATA

　　20,Something Not To Be Loaded,Accounting,"Virginia,USA"

　　4

　　*****

　　position的列子

　　LOAD DATA

　　INFILE *

　　INTO TABLE DEPT

　　REPLACE

　　( DEPTNO

　　position(1:2),

　　DNAME

　　position(*:16),

　　// 这个字段的开始位置在前一字段的结束位置

　　LOC

　　position(*:29),

　　ENTIRE_LINE position(1:29)

　　)

　　BEGINDATA

　　10Accounting

　　Virginia,USA

　　5

　　*****

　　使用函数

　　日期的一种表达

　　TRAILING NULLCOLS的使用

　　LOAD DATA

　　INFILE *

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ','

　　TRAILING NULLCOLS

　　//

　　其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应

　　//

　　的列的值的 如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　// 使用函数

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED date 'dd/mm/yyyy',

　　// 日期的一种表达方式 还有'dd-mon-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

　　6

　　*****

　　使用自定义的函数

　　// 解决的时间问题

　　使用函数这仅适合于常规导入，并不适合 direct导入方式 9i可能可以

　　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;

　　/

　　LOAD DATA

　　INFILE *

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ','

　　TRAILING NULLCOLS

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED "my_to_date( :last_updated )"

　　// 使用自定义的函数

　　)

　　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

　　7

　　*****

　　合并多行记录为一行记录

　　LOAD DATA

　　INFILE *

　　concatenate 3

　　// 通过关键字concatenate 把几行的记录看成一行记录

　　INTO TABLE DEPT

　　replace

　　FIELDS TERMINATED BY ','

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED date 'dd/mm/yyyy'

　　)

　　BEGINDATA

　　10,Sales,

　　// 其实这3行看成一行

　　10,Sales,Virginia,1/5/2000

　　Virginia,

　　1/5/2000

　　// 这列子用 continueif list="," 也可以

　　告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行

　　LOAD DATA

　　INFILE *

　　continueif this(1:1) = '-'

　　// 找每行的开始是否有连接字符 -

　　有就把下一行连接为一行

　　// 如

　　-10,Sales,Virginia,

　　//

　　1/5/2000

　　就是一行

　　10,Sales,Virginia,1/5/2000

　　// 其中1:1 表示从第一行开始 并在第一行结束

　　还有continueif next 但continueif list最理想

　　INTO TABLE DEPT

　　replace

　　FIELDS TERMINATED BY ','

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED date 'dd/mm/yyyy'

　　)

　　BEGINDATA

　　// 但是好象不能象右面的那样使用

　　-10,Sales,Virginia,

　　-10,Sales,Virginia,

　　1/5/2000

　　1/5/2000

　　-40,

　　40,Finance,Virginia,13/04/2001

　　Finance,Virginia,13/04/2001

　　================================ 用别的语言帮助解决的方法

　　txt文件中的每2行作为一个记录插入到数据库中的一条记录,文件是定长的

　　1

　　2

　　3

　　4

　　5

　　6

　　7

　　插入数据记录是

　　1 2 3 4 5 6 7

　　-------------

　　可以把换行符作为一个分隔符来处理

　　-------------

　　1、到www.activeperl.com去下载一个activeperl5.6 MSI

　　2、安装 PERL

　　3、你的文本文件示例：test.old

　　1 2 3 4 5

　　6 7

　　a b c d e

　　f g

　　4、我的PERL程序：test.pl

　　$mycount=2;

　　open(FILE_OLD","TEST.OLD");

　　open(FILE_NEW",">TEST.NEW");

　　while(<FILE_OLD>)

　　{

　　chomp;

　　if ($mycount%2 == 0)

　　{print FILE_NEW $_." ";}

　　else

　　{print FILE_NEW $_."\n";}

　　$mycount++;

　　}

　　5、在命令窗口下执行 perl test.pl

　　6、得到一个新的文本文件：test.new,内容如下：

　　1 2 3 4 5 6 7

　　a b c d e f g

　　---------------

　　load data

　　infile 'test.txt'

　　concatenate(2)

　　into table aa

　　fields terminated by whitespace

　　(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7)

　　==============================================================

　　8

　　*****

　　载入每行的行号

　　load data

　　infile *

　　into table t

　　replace

　　( seqno

　　RECNUM

　　//载入每行的行号

　　text Position(1:1024))

　　BEGINDATA

　　fsdfasj

　　//自动分配一行号给载入 表t 的seqno字段

　　此行为 1

　　fasdjfasdfl

　　//

　　此行为 2

　　...

　　9

　　*****

　　载入有换行符的数据

　　注意:

　　unix 和 windows 不同

　　\\n

　　&

　　/n

　　还可以用 dbms_lob

　　和 bfile 看一个文件的回车 换行 等其他特殊字符

　　< 1 >

　　使用一个非换行符的字符

　　LOAD DATA

　　INFILE *

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ','

　　TRAILING NULLCOLS

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED "my_to_date( :last_updated )",

　　COMMENTS

　　"replace(:comments,'\n',chr(10))"

　　//

　　replace 的使用帮助转换换行符

　　)

　　BEGINDATA

　　10,Sales,Virginia,01-april-2001,This is the Sales\nOffice in Virginia

　　20,Accounting,Virginia,13/04/2001,This is the Accounting\nOffice in Virginia

　　30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting\nOffice in Virginia

　　40,Finance,Virginia,987268297,This is the Finance\nOffice in Virginia

　　< 2 >

　　使用fix属性

　　LOAD DATA

　　INFILE demo17.dat "fix 101"

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ','

　　TRAILING NULLCOLS

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED "my_to_date( :last_updated )",

　　COMMENTS

　　)

　　demo17.dat

　　10,Sales,Virginia,01-april-2001,This is the Sales

　　Office in Virginia

　　20,Accounting,Virginia,13/04/2001,This is the Accounting

　　Office in Virginia

　　30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting

　　Office in Virginia

　　40,Finance,Virginia,987268297,This is the Finance

　　Office in Virginia

　　//

　　这样装载会把换行符装入数据库

　　下面的方法就不会 但要求数据的格式不同

　　LOAD DATA

　　INFILE demo18.dat "fix 101"

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

　　TRAILING NULLCOLS

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED "my_to_date( :last_updated )",

　　COMMENTS

　　)

　　demo18.dat

　　10,Sales,Virginia,01-april-2001,"This is the Sales

　　Office in Virginia"

　　20,Accounting,Virginia,13/04/2001,"This is the Accounting

　　Office in Virginia"

　　30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting

　　Office in Virginia"

　　40,Finance,Virginia,987268297,"This is the Finance

　　Office in Virginia"

　　< 3 >

　　使用var属性

　　LOAD DATA

　　INFILE demo19.dat "var 3"

　　// 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ','

　　TRAILING NULLCOLS

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED "my_to_date( :last_updated )",

　　COMMENTS

　　)

　　demo19.dat

　　07110,Sales,Virginia,01-april-2001,This is the Sales

　　Office in Virginia

　　07820,Accounting,Virginia,13/04/2001,This is the Accounting

　　Office in Virginia

　　08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting

　　Office in Virginia

　　07140,Finance,Virginia,987268297,This is the Finance

　　Office in Virginia

　　< 4 >

　　使用str属性

　　// 最灵活的一中 可定义一个新的行结尾符

　　win 回车换行 : chr(13)||chr(10)

　　此列中记录是以 a|\r\n 结束的

　　select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;

　　结果

　　7C0D0A

　　LOAD DATA

　　INFILE demo20.dat "str X'7C0D0A'"

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ','

　　TRAILING NULLCOLS

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED "my_to_date( :last_updated )",

　　COMMENTS

　　)

　　demo20.dat

　　10,Sales,Virginia,01-april-2001,This is the Sales

　　Office in Virginia|

　　20,Accounting,Virginia,13/04/2001,This is the Accounting

　　Office in Virginia|

　　30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting

　　Office in Virginia|

　　40,Finance,Virginia,987268297,This is the Finance

　　Office in Virginia|

　　10

　　*****

　　将数据导入多个表

　　LOAD DATA

　　INFILE *

　　REPLACE

　　INTO TABLE emp WHEN empno != ' '

　　(

　　empno POSITION(1:4) INTEGER EXTERNAL,

　　ename POSITION(6:15) CHAR,

　　deptno POSITION(17:18) CHAR,

　　mgr POSITION(20:23) INTEGER EXTERNAL

　　)

　　INTO TABLE proj

　　WHEN projno != ' '

　　(

　　projno POSITION(25:27) INTEGER EXTERNAL,

　　empno POSITION(1:4) INTEGER EXTERNAL

　　)

　　11

　　*****

　　转载 RAW 数据 或 转载长字段

　　options(bindsize=1075700,rows=1)

　　load data

　　infile my.data "fix 53760"

　　// 53760 * 20=1075700

　　1075700是小于64K 的最大因子

　　concatenate 20

　　preserve blanks

　　into table foo

　　append

　　(id constant 1,bigdata raw(1075700))

　　12

　　*****

　　转载 LOB 数据

　　用 dbms_lob

　　dbms_lob 转载的数据要在服务器上 通过网络的不行

　　drop table demo;

　　create or replace directory dir1

　　as 'c:\temp\';

　　create or replace directory "dir2" as 'c:\temp\';

　　create table demo

　　( id

　　int primary key,

　　theClob

　　clob

　　)

　　/

　　host echo 'Hello World\!' > c:\temp\test.txt

　　declare

　　l_clob

　　clob;

　　l_bfile

　　bfile;

　　begin

　　insert into demo values ( 1, empty_clob() )

　　returning theclob into l_clob;

　　l_bfile := bfilename( 'DIR1', 'test.txt' );

　　-- DIR1 要大写

　　dbms_lob.fileopen( l_bfile );

　　dbms_lob.loadfromfile( l_clob, l_bfile,

　　dbms_lob.getlength( l_bfile ) );

　　dbms_lob.fileclose( l_bfile );

　　end;

　　/

　　select dbms_lob.getlength(theClob), theClob from demo

　　/

　　----------------------------------

　　用 sqlldr

　　在同一行的LOB

　　lob数据在同一个数据文件中

　　LOAD DATA

　　INFILE demo21.dat "str X'7C0D0A'"

　　INTO TABLE DEPT

　　REPLACE

　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

　　TRAILING NULLCOLS

　　(DEPTNO,

　　DNAME

　　"upper(:dname)",

　　LOC

　　"upper(:loc)",

　　LAST_UPDATED "my_to_date( :last_updated )",

　　COMMENTS

　　char(1000000)

　　)

　　10,Sales,Virginia,01-april-2001,This is the Sales

　　Office in Virginia|

　　20,Accounting,Virginia,13/04/2001,This is the Accounting

　　Office in Virginia|

　　30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting

　　Office in Virginia|

　　40,Finance,Virginia,987268297,"This is the Finance

　　Office in Virginia, it has embedded commas and is

　　much longer then the other comments field.

　　If you

　　feel the need to add double quoted text in here like

　　this: ""You will need to double up those quotes!"" to

　　preserve them in the string.

　　This field keeps going for upto

　　1,000,000 bytes or until we hit the magic end of record marker,

　　the | followed by a end of line -- it is right here ->"|

　　------------------------------------------------------

　　用 sqlldr

　　不在同一行的LOB

　　就是lob数据在单独的文件中

　　create table lob_demo

　　( owner

　　varchar2(255),

　　timestamp date,

　　filename

　　varchar2(255),

　　text

　　clob

　　)

　　/

　　LOAD DATA

　　///////////

　　window 的

　　INFILE *

　　REPLACE

　　INTO TABLE LOB_DEMO

　　( owner

　　position(40:61),

　　timestamp

　　position(1:18) "to_date(:timestamp||'m','mm/dd/yyyy

　　hh:miam')",

　　filename

　　position(63:80),

　　-- 下面的LOB的filename是从这里来的

　　text LOBFILE(filename) TERMINATED BY EOF

　　)

　　BEGINDATA

　　04/14/2001

　　12:36p

　　1,697 BUILTIN\Administrators demo10.log

　　// 这是 windows 下面的情况 上面的数据是用 dir /q/n 看见的情况

　　*******

　　///// unix 下的情况

　　用 ls -l 得到上面数据的情况

　　控制文件就改下时间的格式

　　------------------------------

　　lob 到对象列

　　create table image_load( id number, name varchar2(255),

　　image ordsys.ordimage )

　　/

　　desc ordsys.ordimage

　　desc ordsys.ordsource

　　LOAD DATA

　　INFILE *

　　INTO TABLE T

　　replace

　　fields terminated by ","

　　(

　　id,

　　name,

　　fiel_name filler,

　　image column object

　　(

　　source column object

　　(

　　localdatalobfile(file_name) terminated by bof

　　nullif file_name='NONE'

　　)

　　)

　　)

　　begindata

　　1,icons,icons.gif

　　13

　　*****

　　转载varrays /嵌套表

　　create type myArrayType

　　as varray(10) of number(12,2)

　　/

　　create table t

　　( x int primary key, y myArrayType )

　　/

　　LOAD DATA

　　INFILE *

　　INTO TABLE T

　　replace

　　fields terminated by ","

　　(

　　x,

　　y_cnt

　　FILLER,

　　y

　　varray count (y_cnt)

　　(

　　y

　　)

　　)

　　BEGINDATA

　　1,2,3,4

　　2,10,1,2,3,4,5,6,7,8,9,10

　　3,5,5,4,3,2,1

　　------------------------------------

　　create or replace type myTableType

　　as table of number(12,2)

　　/

　　create table t

　　( x int primary key, y myTableType )

　　nested table y store as y_tab

　　/

　　LOAD DATA

　　INFILE *

　　INTO TABLE T

　　replace

　　fields terminated by ","

　　(

　　x,

　　y

　　nested table count (CONSTANT 5)

　　(

　　y

　　)

　　)

　　BEGINDATA

　　1,100,200,300,400,500

　　2,123,243,542,123,432

　　==============================================================================

　　象这样的数据 用 nullif 子句

　　10-jan-200002350Flipper seemed unusually hungry today.

　　10510-jan-200009945Spread over three meals.

　　id position(1:3) nullif id=blanks

　　// 这里可以是blanks 或者别的表达式

　　//

　　下面是另一个列子 第一行的 1 在数据库中将成为 null

　　LOAD DATA

　　INFILE *

　　INTO TABLE T

　　REPLACE

　　(n position(1:2) integer external nullif n='1',

　　v position(3:8)

　　)

　　BEGINDATA

　　1

　　10

　　20lg

　　------------------------------------------------------------

　　如果是英文的日志 格式，可能需要修改环境变量 nls_lang or nls_date_format