SQLLOADER 4 position

最新推荐文章于 2017-12-21 10:31:00 发布

cnk45946

最新推荐文章于 2017-12-21 10:31:00 发布

阅读量600

点赞数

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

一描述

此档是sqlloader学习渐进的第四篇.当源flat数据中各列间并没有明显的分隔符时,可以使用position来界定各列.

二操作环境

OS info

windows server 2003

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 17:34:22 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>

other

脚本目录:C:\sqlloader_exec\ppt_case4

三过程设计

1.准备的flat源数据.
10Accounting Virginia,USA
(第一列与第二列间无间系)

2.相关文件ppt_case4.sql
rem
rem
rem

set termout off

droptabledept_p4;
create table dept_p4 (deptno number(2),dname char(20),loc char(20));
exit;

3.相关文件ppt_case4_1.sql
rem
rem
rem

set termout off

alter table dept_p4 add (entire_line char(30));
exit;

4.相关文件ppt_case4.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_P4
REPLACE
( DEPTNO position(1:2),
DNAME position(*:16),
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA

5.以scott用户登录数据库,运行ppt_case4.sql初始化环境
6.在cmd命令下执行sqlldr加载ppt_case4.ctl控制文件命令
7.查看sqlldr日志信息
8.查看sqlldr bad日志信息
9.查看数据库加载成功的数据

四详细步骤操作

1.以scott用户登录数据库,运行ppt_case4.sql初始化环境
C:\sqlloader_exec\ppt_case4>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 17:45:56 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> @ppt_case4
Disconnected from 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_case4>

2.在cmd命令下执行sqlldr加载ppt_case4.ctl控制文件命令
C:\sqlloader_exec\ppt_case4>sqlldr userid=scott/tiger control=ppt_case4.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 17:49:15 2012

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

SQL*Loader-466: Column ENTIRE_LINE does not exist in table DEPT_P4.

C:\sqlloader_exec\ppt_case4>

(报错是因为目的表中没有ENTIRE_LINE这一列定义,所以sqlloader加载由于列不匹配所以加载不成功.运行ppt_case4_1.sql向scott.dept_p4表中添加entire_line列
C:\sqlloader_exec\ppt_case4>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 17:50:53 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> @ppt_case4_1
Disconnected from 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_case4>
--继续执行sqlldr加载ppt_case4.ctl控制文件命令
C:\sqlloader_exec\ppt_case4>sqlldr userid=scott/tiger control=ppt_case4.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 17:52:07 2012

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

Commit point reached - logical record count 1

C:\sqlloader_exec\ppt_case4>
)

3.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 17:52:07 2012

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

Control File:   ppt_case4.ctl
Data File:      ppt_case4.ctl
Bad File:     ppt_case4.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_P4, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                                1:2     2           CHARACTER
DNAME                             NEXT:16    16           CHARACTER
LOC                               NEXT:29    29           CHARACTER
ENTIRE_LINE                          1:29    29           CHARACTER

Table DEPT_P4:
1 Row 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:                   5504 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Aug 05 17:52:07 2012
Run ended on Sun Aug 05 17:52:08 2012

Elapsed time was:     00:00:00.85
CPU time was:         00:00:00.00

4.查看数据库加载成功的数据
C:\sqlloader_exec\ppt_case4>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 17:56:30 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> select * from dept_p4;

    DEPTNO DNAME                LOC                  ENTIRE_LINE
---------- -------------------- -------------------- ------------------------------
        10 Accounting Vir       ginia,USA            10Accounting Virginia,USA

SQL>

五个人总结

关于position的用法:

示例中控制文件对position为绝对引用,例:

(
col_1 position(1:2),
col_2 position(3:10), //绝对偏移量(包含空格符占用位)
col_3 position(*:16), // 相对偏移量,从前一字段的结束位置开始,此例为11:16
col_3 position(*+3:25), //相对偏移量,此例为20:25
col_4 position(*) char(20), //相对偏移量,后接定长类型.此例为:26:46
)