一描述

Case Study 4: Loading Combined Physical Records: Combines multiple physical records into one logical record corresponding to one database row.

Case 4 demonstrates:

Combining multiple physical records to form. one logical record with CONTINUEIF;
Inserting negative numbers.
Indicating with REPLACE that the table should be emptied before the new data is inserted;
Specifying a discard file in the control file using DISCARDFILE;
Specifying a maximum number of discards using DISCARDMAX;
Rejecting records due to duplicate values in a unique index or due to invalid data values;

二操作环境

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:\oracle\product\9.2.0\db_1\rdbms\demo

三过程设计

1.准备的源flat数据 ulcase4.dat
*7782 CLARK      MA
NAGER   7839 2572.50   -10    2512-NOV-85
*7839 KING       PR
ESIDENT       5500.00          2505-APR-83
*7934 MILLER     CL
ERK     7782   920.00          2508-MAY-80
*7566 JONES      MA
NAGER   7839 3123.75          2517-JUL-85
*7499 ALLEN      SA
LESMAN 7698 1600.00   300.00 25 3-JUN-84
*7654 MARTIN     SA
LESMAN 7698 1312.50 1400.00 2521-DEC-85
*7658 CHAN       AN
ALYST   7566 3450.00          2516-FEB-84
*     CHEN       AN
ALYST   7566 3450.00          2516-FEB-84
*7658 CHIN       AN
ALYST   7566 3450.00          2516-FEB-84

2.相关文件ulcase4.sql
rem
rem $Header: ulcase4.sql 14-jul-99.14:24:22 mjaeger Exp $
rem
rem Copyright (c) 1990, 1999, Oracle Corporation. All rights reserved.
rem
rem NAME
rem
rem FUNCTION
rem NOTES
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   Heigham    11/21/90 - create UNIQUE index
rem

set termout off

rem host write sys$output "Building case 4 demonstration tables. Please wait"

drop table emp;

create table emp
       (empno number(4) not null,
        ename char(10),
        job char(9),
        mgr number(4),
        hiredate date,
        sal number(7,2),
        comm number(7,2),
        deptno number(2));

create unique index empix on emp(empno);

exit

3.相关文件ulcase4.ctl
-- Copyright (c) 1991 by Oracle Corporation
--   NAME
--     ulcase4.ctl -
--   DESCRIPTION
--
--   RETURNS
--
--   NOTES
--
--   MODIFIED   (MM/DD/YY)
--    ksudarsh   11/06/92 - infile is ulcase4
--    cheigham   08/28/91 - Creation
--
-- $Header: ulcase4.ctl,v 1.2 1992/11/06 11:09:40 KSUDARSH Exp $ case4.ctl
--
LOAD DATA
INFILE "ulcase4.dat"
DISCARDFILE "ulcase4.dis"
DISCARDMAX 999
REPLACE
CONTINUEIF (1) = '*'
INTO TABLE EMP

( EMPNO    POSITION(01:04) INTEGER EXTERNAL,
ENAME    POSITION(06:15) CHAR,
JOB      POSITION(17:25) CHAR,
MGR      POSITION(27:30) INTEGER EXTERNAL,
SAL      POSITION(32:39) DECIMAL EXTERNAL,
COMM     POSITION(41:48) DECIMAL EXTERNAL,
DEPTNO   POSITION(50:51) INTEGER EXTERNAL,
HIREDATE POSITION(52:60) INTEGER EXTERNAL)

四详细步骤操作

1.以scott用户连接数据库
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on 星期一 8月 6 20:31:31 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用户运行ulcase4.sql初始化环境
SQL> @ulcase4
从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加载ulcase4.ctl控制文件命令
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr USERID=scott/tiger CONTROL=ulcase4.ctl LOG=ulcase4.log

SQL*Loader: Release 9.0.1.1.1 - Production on 星期一 8月 6 20:34:59 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

达到提交点，逻辑记录计数9

C:\oracle\product\9.2.0\db_1\rdbms\demo>

4.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on 星期一 8月 6 20:34:59 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

控制文件: ulcase4.ctl
数据文件: ulcase4.dat
错误文件: ulcase4.bad
废弃文件: ulcase4.dis:
(允许废弃 999)

加载数: ALL
跳过数: 0
允许的错误: 50
绑定数组: 64 行，最大 256000 字节
继续:    1: 1 =0X2a（字符'*'）,在当前的物理记录中
所用路径:       常规

表EMP
已加载从每个逻辑记录
插入选项对此表REPLACE生效

   列名                        位置      长度中止包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO                                1：4     4           CHARACTER
ENAME                               6：15    10           CHARACTER
JOB                                17：25     9           CHARACTER
MGR                                27：30     4           CHARACTER
SAL                                32：39     8           CHARACTER
COMM                               41：48     8           CHARACTER
DEPTNO                             50：51     2           CHARACTER
HIREDATE                           52：60     9           CHARACTER

记录 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: 无效的月份

记录 8: 被拒绝 - 表 EMP 的列 HIREDATE 出现错误。
ORA-01843: 无效的月份

记录 9: 被拒绝 - 表 EMP 的列 HIREDATE 出现错误。
ORA-01843: 无效的月份

表EMP:
0 行加载成功
由于数据错误, 9 行没有加载。
由于所有 WHEN 子句失败, 0 行没有加载。
由于所有字段都为空的, 0 行没有加载。

为结合数组分配的空间:     4608字节（64行）
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:        0
读取的逻辑记录总数:        9
拒绝的逻辑记录总数:        9
废弃的逻辑记录总数:        0

从星期一 8月 06 20:34:59 2012开始运行
在星期一 8月 06 20:35:00 2012处运行结束

经过时间为: 00: 00: 00.97
CPU 时间为: 00: 00: 00.04(可?

(汗:日志中报错又一次是系统语言环境导致的日期格式问题.重新设置环境变量再次加载:
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=ulcase4.ctl LOG=ulcase4.log

SQL*Loader: Release 9.0.1.1.1 - Production on Mon Aug 6 20:38:21 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Commit point reached - logical record count 9

C:\oracle\product\9.2.0\db_1\rdbms\demo>

查看日志内容:

SQL*Loader: Release 9.0.1.1.1 - Production on Mon Aug 6 20:38:21 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Control File:   ulcase4.ctl
Data File:      ulcase4.dat
Bad File:     ulcase4.bad
Discard File: ulcase4.dis
(Allow 999 discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:   1:1 = 0X2a(character '*'), in current physical record
Path used:      Conventional

Table EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO                                 1:4     4           CHARACTER
ENAME                                6:15    10           CHARACTER
JOB                                 17:25     9           CHARACTER
MGR                                 27:30     4           CHARACTER
SAL                                 32:39     8           CHARACTER
COMM                                41:48     8           CHARACTER
DEPTNO                              50:51     2           CHARACTER
HIREDATE                            52:60     9           CHARACTER

Record 8: Rejected - Error on table EMP.
ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")

Record 9: Rejected - Error on table EMP.
ORA-00001: unique constraint (SCOTT.EMPIX) violated

Table EMP:
7 Rows successfully loaded.
2 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:                   4608 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             9
Total logical records rejected:         2
Total logical records discarded:        0

Run began on Mon Aug 06 20:38:21 2012
Run ended on Mon Aug 06 20:38:22 2012

Elapsed time was:     00:00:01.39
CPU time was:         00:00:00.03

(OK 成功) 切记系统语言环境与时间格式的关系)

5.查看sqlldr bad文件信息
*     CHEN       AN
ALYST   7566 3450.00          2516-FEB-84
*7658 CHIN       AN
ALYST   7566 3450.00          2516-FEB-84

五个人总结

又一次的语言环境与时间格式不匹配而导致的数据加载不进去的情况. 注意控制文件中CONTINUEIF (1) = '*' 会自动将无'*'开始的行第1个字符抹去.

六资料参考引用

Oracle9i Database Utilities
Release 2 (9.2)
Part Number A96652-01

http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch10.htm#1007053

ITPub博客

SQLLDR Case Study 4: Loading Combined Physical Records

一描述

二操作环境

OS info

DB info

other

三过程设计

四详细步骤操作

五个人总结

六资料参考引用

最新文章

ITPub博客

SQLLDR Case Study 4: Loading Combined Physical Records

一 描述

二 操作环境

OS info

DB info

other

三 过程设计

四 详细步骤操作

五 个人总结

六 资料参考引用

最新文章

一描述

二操作环境

三过程设计

四详细步骤操作

五个人总结

六资料参考引用