用 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 er,
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.ordimagedesc ordsys.ordsourceLOAD 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
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 er,
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.ordimagedesc ordsys.ordsourceLOAD 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
创建控制文件in.ctlload data
append
into table t_ljdj
fields terminated by '|'
(
cp_bh char(30),
dj blob
)
执行sqlldr username/pw control=c:\in.ctl data=c:\in.txt
这样的方法还不如用程序来读写,这样容易判断,而且操作方便。
LGQDUCKY,主要是执行速度的关系,用SQLLDR导入比你说的用程序来读写很多时候甚至要快一个数量级。如果是导出那我用EXP生成DMP文件就可以了,但导入的话我就不会直接写DMP文件了:(