一 描述
Case Study 3: Loading a Delimited, Free-Format File: Loads data from stream format records with delimited fields and sequence numbers. The data is found at the end of the control file.
Case 3 demonstrates:
- Loading data (enclosed and terminated) in stream format.
- Loading dates using the datatype DATE.
- Using SEQUENCE numbers to generate unique keys for loaded data.
- Using APPEND to indicate that the table need not be empty before inserting new records.
- Using Comments in the control file set off by two hyphens.
二 操作环境
OS info
windows server 2003 32bit
DB info
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "sys/change_on_install as sysdba"SQL*Plus: Release 9.0.1.0.1 - Production on 星期日 8月 5 11:22:43 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 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> archive log list;
数据库日志模式 非存档模式
自动存档 禁用
存档终点 C:\oracle\product\9.2.0\db_1\RDBMS
最早的概要日志序列 2
当前日志序列 4
SQL>
other
脚本目录:C:\oracle\product\9.2.0\db_1\rdbms\demo三 过程设计
1.相关文件ulcase3.sqlrem
rem $Header: ulcase3.sql 14-jul-99.14:23:36 mjaeger Exp $
rem
rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved.
rem
rem NAME
rem ulcase3.sql -
rem DESCRIPTION
rem
rem RETURNS
rem
rem NOTES
rem ulcase2.sql must be executed before this pocedure.
rem MODIFIED (MM/DD/YY)
rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines
rem jstenois 06/17/99 - cleanup tables before load and show feedback
rem ksudarsh 03/11/93 - comment out vms specific host command
rem ksudarsh 12/29/92 - Creation
rem cheigham 08/28/91 - Creation
rem
set termout off
rem Do not clean up table because this example shows appending to existing
rem rows in table that also has new columns.
rem host write sys$output "Adding columns to emp. Please wait."
alter table emp add (projno number, loadseq number);
exit
2.相关文件ulcase3.ctl
-- Copyright (c) 1991 by Oracle Corporation
-- NAME
-- ulcase3.ctl -
-- DESCRIPTION
--
-- RETURNS
--
-- NOTES
--
-- MODIFIED (MM/DD/YY)
-- cheigham 08/28/91 - Creation
--
-- $Header: ulcase3.ctl,v 1.1 1991/09/02 14:52:19 CHEIGHAM Stab $ case3.ctl
--
-- Variable length, delimited and enclosed data format
LOAD DATA
INFILE *
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
hiredate DATE(20) "DD-Month-YYYY",
sal, comm,
deptno CHAR TERMINATED BY ':',
projno,
loadseq SEQUENCE(MAX,1) )
BEGINDATA
7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101
7839, "King", "President", , 17-November-1981, 5500.00,, 10:102
7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102
7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101
7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00, 300.00, 30:103
7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103
7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101
四 详细步骤操作
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"
SQL*Plus: Release 9.0.1.0.1 - Production on 星期日 8月 5 11:35:41 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>
2.以scott用户执行ulcase3.sql初始化环境脚本.
SQL> @ulcase3
从Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production中断开
C:\oracle\product\9.2.0\db_1\rdbms\demo>
3.在cmd环境执行sqlldr命令加载ulcase3.ctl控制文件
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr userid=scott/tiger control=ulcase3.ctl log=ulcase3.g
SQL*Loader: Release 9.0.1.1.1 - Production on 星期日 8月 5 11:38:08 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
达到提交点，逻辑记录计数7
C:\oracle\product\9.2.0\db_1\rdbms\demo>
4.查看ulcase3.log日志内容.
SQL*Loader: Release 9.0.1.1.1 - Production on 星期日 8月 5 11:38:08 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
控制文件: ulcase3.ctl
数据文件: ulcase3.ctl
错误文件: ulcase3.bad
废弃文件: 未作指定
:
(可废弃所有记录)
加载数: ALL
跳过数: 0
允许的错误: 50
绑定数组: 64 行，最大 256000 字节
继续: 未作指定
所用路径: 常规
表EMP
已加载从每个逻辑记录
插入选项对此表APPEND生效
列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , O (") CHARACTER
ENAME NEXT * , O (") CHARACTER
JOB NEXT * , O (") CHARACTER
MGR NEXT * , O (") CHARACTER
HIREDATE NEXT 20 , O (") DATE DD-Month-YYYY
SAL NEXT * , O (") CHARACTER
COMM NEXT * , O (") CHARACTER
DEPTNO NEXT * : O (") CHARACTER
PROJNO NEXT * , O (") CHARACTER
LOADSEQ SEQUENCE (MAX, 1)
记录 1: 被拒绝 - 表 EMP 的列 HIREDATE 出现错误。
ORA-01843: 无效的月份
记录 2: 被拒绝 - 表 EMP 的列 HIREDATE 出现错误。
ORA-01843: 无效的月份
记录 3: 被拒绝 - 表 EMP 的列 HIREDATE 出现错误。
ORA-01843: 无效的月份
记录 4: 被拒绝 - 表 EMP 的列 HIREDATE 出现错误。
ORA-01843: 无效的月份
记录 5: 被拒绝 - 表 EMP 的列 HIREDATE 出现错误。
ORA-01843: 无效的月份
记录 6: 被拒绝 - 表 EMP 的列 HIREDATE 出现错误。
ORA-01843: 无效的月份
记录 7: 被拒绝 - 表 EMP 的列 HIREDATE 出现错误。
ORA-01843: 无效的月份
表EMP:
0 行加载成功
由于数据错误, 7 行没有加载。
由于所有 WHEN 子句失败, 0 行没有加载。
由于所有字段都为空的, 0 行没有加载。
为结合数组分配的空间: 134976字节（64行）
读取 缓冲区字节数: 1048576
跳过的逻辑记录总数: 0
读取的逻辑记录总数: 7
拒绝的逻辑记录总数: 7
废弃的逻辑记录总数: 0
从星期日 8月 05 11:38:08 2012开始运行
在星期日 8月 05 11:38:10 2012处运行结束
经过时间为: 00: 00: 01.02
CPU 时间为: 00: 00: 00.00(可?
5.日志显示数据并未导入成功,查找"ORA-01843: 无效的月份"错误,由于日期语言环境的问题导致源数据中月份与数据库期望的月份格式不匹配.重设语言环境后,再次运行3步.
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "sys/change_on_install as sysdba"
SQL*Plus: Release 9.0.1.0.1 - Production on 星期日 8月 5 11:55:06 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 100
SQL> col value for a30
SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET ZHS16GBK
SQL> exit
从Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production中断开
C:\oracle\product\9.2.0\db_1\rdbms\demo>set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr userid=scott/tiger control=ulcase3.ctl log=ulcase3.log
SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 11:56:30 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 7
C:\oracle\product\9.2.0\db_1\rdbms\demo>
6.再次查看ulcase3.log日志内容.
SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 11:56:30 2012
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Control File: ulcase3.ctl
Data File: ulcase3.ctl
Bad File: ulcase3.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 EMP, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , O(") CHARACTER
ENAME NEXT * , O(") CHARACTER
JOB NEXT * , O(") CHARACTER
MGR NEXT * , O(") CHARACTER
HIREDATE NEXT 20 , O(") DATE DD-Month-YYYY
SAL NEXT * , O(") CHARACTER
COMM NEXT * , O(") CHARACTER
DEPTNO NEXT * : O(") CHARACTER
PROJNO NEXT * , O(") CHARACTER
LOADSEQ SEQUENCE (MAX, 1)
Table EMP:
7 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: 134976 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 7
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Sun Aug 05 11:56:30 2012
Run ended on Sun Aug 05 11:56:31 2012
Elapsed time was: 00:00:00.87
CPU time was: 00:00:00.03
7.查看加载到scott用户下emp表的内容.(列显示不下,这里未显示表中全部的列)
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"
SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 12:04:26 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 120
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 2572.5 10
7839 KING PRESIDENT 5500 10
7934 MILLER CLERK 7782 920 10
7566 JONES MANAGER 7839 3123.75 20
7499 ALLEN SALESMAN 7698 1600 300 30
7654 MARTIN SALESMAN 7698 1312.5 1400 30
7658 CHAN ANALYST 7566 3450 20
7782 Clark Manager 7839 09-JUN-81 2572.5 10
7839 King President 17-NOV-81 5500 10
7934 Miller Clerk 7782 23-JAN-82 920 10
7566 Jones Manager 7839 02-APR-81 3123.75 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 Allen Salesman 7698 20-FEB-81 1600 300 30
7654 Martin Salesman 7698 28-SEP-81 1312.5 1400 30
7658 Chan Analyst 7566 03-MAY-82 3450 20
14 rows selected.
SQL>
五 个人总结
case3是接着case2做的实验,因ulcase3.ctl中为"APPEND"参数,因此scott.emp表中留有case2的数据.
日期语言显示部分与系统设置相关,此问题的出现次证明一切要从实际出发.实验一定要经由自己的环境亲手测试才会有保证.
ps. 文档格式显示中的下划线问题为博客软件问题,没有解决.
六 资料参考引用
Oracle9i Database Utilities Release 1 (9.0.1) Part Number A90192-01
http://www.cnblogs.com/rootq/archive/2009/05/16/1458090.html
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-739865/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-739865/
1743
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
