SQLLDR Case Study 2: Loading Fixed-Format Fields

最新推荐文章于 2024-08-27 09:43:06 发布

cnk45946

最新推荐文章于 2024-08-27 09:43:06 发布

阅读量105

点赞数

文章标签：数据库操作系统

一描述

Case Study 2: Loading Fixed-Format Fields: Loads data from a separate datafile.

Case 2 demonstrates:

A separate datafile.
Data conversions.

In this case, the field positions and datatypes are specified explicitly.

二操作环境

OS info

OS: 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 09:55:33 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

other

脚本目录:C:\oracle\product\9.2.0\db_1\rdbms\demo

三过程设计

1.相关文件ulcase1.sql
rem
rem $Header: ulcase1.sql 14-jul-99.14:22:19 mjaeger Exp $
rem
rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved.
rem
rem    NAME
rem      ulcase1.sql -
rem    DESCRIPTION
rem
rem    RETURNS
rem
rem    NOTES
rem
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/01/93 - comment out vms specific host command
rem     ksudarsh   12/29/92 - Creation
rem     cheigham   08/28/91 - Creation
rem

set termout off

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

drop table emp;
drop table dept;

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 table dept
       (deptno number(2),
        dname char(14) ,
        loc char(13) ) ;

exit

2.相关文件ulcase2.ctl
-- Copyright (c) 1991 by Oracle Corporation
--   NAME
--     ulcase2.ctl -
--   DESCRIPTION
--
--   RETURNS
--
--   NOTES
--
--   MODIFIED   (MM/DD/YY)
--    ksudarsh   04/08/94 - merge changes from branch 1.3.710.1
--    ksudarsh   02/21/94 - quote dat file
--    ksudarsh   03/11/93 - make filename lowercase
--    ksudarsh   11/06/92 - infile is ulcase2
--    cheigham   08/28/91 - Creation
--
-- $Header: ulcase2.ctl,v 1.4 1994/04/08 13:42:44 ksudarsh Exp $ case2.ctl
--
LOAD DATA
INFILE 'ulcase2.dat'
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)

3.相关文件 ulcase2.dat
7782 CLARK      MANAGER   7839 2572.50          10
7839 KING       PRESIDENT       5500.00          10
7934 MILLER     CLERK     7782   920.00          10
7566 JONES      MANAGER   7839 3123.75          20
7499 ALLEN      SALESMAN 7698 1600.00   300.00 30
7654 MARTIN     SALESMAN 7698 1312.50 1400.00 30
7658 CHAN       ANALYST   7566 3450.00          20

四详细步骤操作

Take the following steps to run the case study. If you have already run case study 1, you can skip to step 3 because the ulcase1.sql script. handles both case 1 and case 2.
1.Start SQL*Plus as scott/tiger by entering the following at your system prompt:
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月 3 23:47:09 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用户执行ulcase1.sql初始化环境脚本.
SQL> @ulcase1
从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命令加载ulcase2.ctl控制文件
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr userid=scott/tiger control=ulcase2.ctl log=ulcase2.log

SQL*Loader: Release 9.0.1.1.1 - Production on 星期日 8月 5 10:42:25 2012

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

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

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

4.查看ulcase2.log日志内容.

SQL*Loader: Release 9.0.1.1.1 - Production on 星期日 8月 5 10:42:25 2012

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

控制文件: ulcase2.ctl
数据文件: ulcase2.dat
错误文件: ulcase2.bad
废弃文件: 未作指定
:
(可废弃所有记录)

加载数: ALL
跳过数: 0
允许的错误: 50
绑定数组: 64 行，最大 256000 字节
继续:    未作指定
所用路径:       常规

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

   列名                        位置      长度中止包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
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

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

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

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

从星期日 8月 05 10:42:25 2012开始运行
在星期日 8月 05 10:42:27 2012处运行结束

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

5.查看加载到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 星期日 8月 5 10:45:00 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> 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

已选择7行。

SQL>

五个人总结

此case为使用sqlloader 控制文件中的position关键字来界定data文件中的数据.同时在控制文件中将数据要加载进入的类型显示的定义出来.例中定义了三种类型:

INTEGER EXTERNAL 整型
CHAR 字符型
DECIMAL EXTERNAL 浮点型

六资料参考引用

SQLLOADER Case Study 1: Loading Variable-Length Data

Oracle9i Database Utilities Release 1 (9.0.1) Part Number A90192-01

SQL Loader的使用详解 http://zhiwenweb.cn/jszx/sjkjs/mssql/201110/30464.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-739859/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11780477/viewspace-739859/

cnk45946

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQLLDR Case Study 2: Loading Fixed-Format Fields

一描述Case Study 2: Loading Fixed-Format Fields: Loads data from a separate datafile. Cas...
复制链接

扫一扫