用 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
解决方案 »
- create secure application问题
- 如何用ODBC连接Oracle数据库
- ORacle9i 的数据如何导入到Sql2005, 没有分了 求大家帮忙了
- inner join和left join有什么区别和联系,什么情况下用inner join 和left join? select ... case...when...then...when...then...是什么意思?
- 求助,一个查询的问题
- 求一个SQL语句,困扰半天了
- 帮忙写个触发器?
- 急问 SQLLDR 的 控制文件的写法
- 关于oracle9建库的问题
- 那位慈心仁厚的大侠,教小弟几招!————Oracle8.0.5的幼稚问题!
- 大难题:在Win2000或者WinXP上安装 Oracle7.3.3 ???
- 寻求ORACLE 9I技术文档
创建控制文件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文件了:(