一 描述
此档是sqlloader学习的第13篇.载入有换行符的数据,目的使用str 参数灵活的定义行结束符.
二 操作环境
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源数据demo20.dat10,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|
(注:每个Virginia单词最后一个字符为 "| " 做为加载行的行尾字符)
2.相关文件ppt_case10_4.sql
ppt_case10_4.sql
rem
rem
rem
set termout off
drop table dept_p10_4;
create table dept_p10_4 (deptno number(2),dname char(20),loc char(20),last_updated date,comments varchar2(100));
exit;
3.相关文件ppt_case10_p4.ctl
LOAD DATA
INFILE demo20.dat "str X'7C200D0A'"
INTO TABLE DEPT_P10_4
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
4.以scott用户登 录 数据库运行ppt_case10_4.sql初始化环境.
5.在cmd命令下执行sqlldr加载ppt_case10_4.ctl控制文件命令
6.查看sqlldr日志信息
7.查看sqlldr bad日志信息
8.查看数据库加载成功的数据
四 详细步骤操作
1.分析flat源数据文件的结尾处.根据结尾情况在数据库中查找控制文件中可以使用的16进制代码,如下SQL> select utl_raw.cast_to_raw('| '||chr(13)||chr(10)) from dual;
UTL_RAW.CAST_TO_RAW('|'||CHR(13)||CHR(10))
--------------------------------------------------------------------------------
7C200D0A
SQL>
2.以scott用户登 录 数据库运行ppt_case10_4.sql初始化环境.
C:\sqlloader_exec\ppt_case10>sqlplus "scott/tiger"
SQL*Plus: Release 9.0.1.0.1 - Production on 星期二 8月 7 13:56:13 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_4
从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>
3.在cmd命令下执行sqlldr加载ppt_case10_4.ctl控制文件命令
C:\sqlloader_exec\ppt_case10>sqlldr "scott/tiger" control=ppt_case10_4.ctl
SQL*Loader: Release 9.0.1.1.1 - Production on 星期二 8月 7 13:56:56 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
达到提交点,逻辑记录计数4
C:\sqlloader_exec\ppt_case10>
4.查看sqlldr日志信息
SQL*Loader: Release 9.0.1.1.1 - Production on 星期二 8月 7 13:56:56 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
控制文件: ppt_case10_4.ctl
数据文件: demo20.dat
文件处理选项字符串: "str X'7C200D0A'"
错误文件: demo20.bad
废弃文件: 未作指定
:
(可废弃所有记录)
加载数: ALL
跳过数: 0
允许的错误: 50
绑定数组: 64 行,最大 256000 字节
继续: 未作指定
所用路径: 常规
表DEPT_P10_4
已加载从每个逻辑记录
插入选项对此表REPLACE生效
TRAILING NULLCOLS 选项生效
列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
列的 SQL 串: "upper(:dname)"
LOC NEXT * , CHARACTER
列的 SQL 串: "upper(:loc)"
LAST_UPDATED NEXT * , CHARACTER
列的 SQL 串: "my_to_date( :last_updated )"
COMMENTS NEXT * , CHARACTER
记录 1: 被拒绝 - 表 DEPT_P10_4 的列 LAST_UPDATED 出现错误。
ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误
ORA-06512: 在"SCOTT.MY_TO_DATE", line 20
表DEPT_P10_4:
3 行加载成功
由于数据错误, 1 行没有加载。
由于所有 WHEN 子句失败, 0 行没有加载。
由于所有字段都为空的, 0 行没有加载。
为结合数组分配的空间: 82560字节(64行)
读取 缓冲区字节数: 1048576
跳过的逻辑记录总数: 0
读取的逻辑记录总数: 4
拒绝的逻辑记录总数: 1
废弃的逻辑记录总数: 0
从星期二 8月 07 13:56:56 2012开始运行
在星期二 8月 07 13:56:57 2012处运行结束
经过时间为: 00: 00: 01.39
CPU 时间为: 00: 00: 00.39(可?
5.查看sqlldr bad日志信息
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia|
6.查看数据库加载成功的数据
C:\sqlloader_exec\ppt_case10>sqlplus "scott/tiger"
SQL*Plus: Release 9.0.1.0.1 - Production on 星期二 8月 7 14:02:03 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> set lines 120
SQL> col comments for a40
SQL> select * from dept_p10_4;
DEPTNO DNAME LOC LAST_UPDAT COMMENTS
---------- -------------------- -------------------- ---------- ----------------------------------------
20 ACCOUNTING VIRGINIA 13-4月 -01 This is the Accounting
Office in Virginia
30 CONSULTING VIRGINIA 14-4月 -01 This is the Consulting
Office in Virginia
40 FINANCE VIRGINIA 14-4月 -01 This is the Finance
Office in Virginia
SQL>
五 个人总结
"10" 行没有加载进入数据库.报错原因为ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误. 为日期语言环境问题导致的加载不成功所致.解决方法参见参考文档.
六 资料参考引用
http://afy.itpub.net/post/1128/22073
SQLLOADER 6 自定义函数 http://space.itpub.net/11780477/viewspace-739972
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-740250/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-740250/