一 描述
此档是sqlloader学习的第10篇.载入有换行符的数据.目的将flat源数据文件中相应的内容使用replace函数替换成相应的换行符.
二 操作环境
OS info
windows server 2003 32bit
DB info
连接到:Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL 9.0.1.1.1 Production
Oracle9i Enterprise Edition 9.0.1.1.1 Production
PL/SQL 9.0.1.1.1 Production
TNS for 32-bit Windows: 9.0.1.1.0 Production
SQL>
other
脚本目录:C:\sqlloader_exec\ppt_case10三 过程设计
1.准备的flat源数据.10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia
(注:)
2.相关文件ppt_case10.sql
rem
rem
rem
set termout off
drop table dept_p10;
create table dept_p10 (deptno number(2),dname char(20),loc char(20),last_updated date,comments varchar2(100));
exit;
3.相关文件ppt_case10.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_P10
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 SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia
4.以scott用户登 录 数据库运行ppt_case10.sql初始化环境.
5.在cmd命令下执行sqlldr加载ppt_case10.ctl控制文件命令.
6.查看sqlldr日志信息
7.查看sqlldr bad日志信息
8.查看数据库加载成功的数据
四 详细步骤操作
1.以scott用户登 录 数据库运行ppt_case10.sql初始化环境.C:\sqlloader_exec\ppt_case10>sqlplus "scott/tiger"
SQL*Plus: Release 9.0.1.0.1 - Production on 星期一 8月 6 21:33:36 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> @ppt_case10
从Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production中断开
C:\sqlloader_exec\ppt_case10>
2.在cmd命令下执行sqlldr加载ppt_case10.ctl控制文件命令
C:\sqlloader_exec\ppt_case10>set nls_lang=AMERICAN_AMERICA.zhs16gbk
C:\sqlloader_exec\ppt_case10>sqlldr userid=scott/tiger control=ppt_case10.ctl
SQL*Loader: Release 9.0.1.1.1 - Production on Mon Aug 6 21:35:11 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
C:\sqlloader_exec\ppt_case10>
3.查看sqlldr日志信息
SQL*Loader: Release 9.0.1.1.1 - Production on Mon Aug 6 21:35:11 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Control File: ppt_case10.ctl
Data File: ppt_case10.ctl
Bad File: ppt_case10.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table DEPT_P10, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
SQL string for column : "upper(:dname)"
LOC NEXT * , CHARACTER
SQL string for column : "upper(:loc)"
LAST_UPDATED NEXT * , CHARACTER
SQL string for column : "my_to_date( :last_updated )"
COMMENTS NEXT * , CHARACTER
SQL string for column : "replace(:comments,'n',chr(10))"
Table DEPT_P10:
4 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 82560 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 4
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Mon Aug 06 21:35:11 2012
Run ended on Mon Aug 06 21:35:12 2012
Elapsed time was: 00:00:00.89
CPU time was: 00:00:00.01
5.查看数据库加载成功的数据
C:\sqlloader_exec\ppt_case10>sqlplus "scott/tiger"
SQL*Plus: Release 9.0.1.0.1 - Production on Mon Aug 6 21:37:29 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> set lines 100
SQL> col comments for a30
SQL> select * from dept_p10;
DEPTNO DNAME LOC LAST_UPDA COMMENTS
---------- -------------------- -------------------- --------- ------------------------------
10 SALES VIRGINIA 01-APR-01 This is the Sales
Office i
Virgi
ia
20 ACCOUNTING VIRGINIA 13-APR-01 This is the Accou
ti
g
Office i
Virgi
ia
DEPTNO DNAME LOC LAST_UPDA COMMENTS
---------- -------------------- -------------------- --------- ------------------------------
30 CONSULTING VIRGINIA 14-APR-01 This is the Co
sulti
g
Office i
Virgi
ia
40 FINANCE VIRGINIA 14-APR-01 This is the Fi
a
ce
DEPTNO DNAME LOC LAST_UPDA COMMENTS
---------- -------------------- -------------------- --------- ------------------------------
Office i
Virgi
ia
SQL>
五 个人总结
从导入数量库中数据显示结果看.flat源数据文件中的comment部分"n"被替换成了换行符,在数据库的相应行里也存储进了换行符.
六 资料参考引用
http://afy.itpub.net/post/1128/22073
百度百科 ASCII http://baike.baidu.com/view/15482.htm来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-740144/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-740144/