SQLLOADER 3 指定不装载哪一列-优快云博客

本文介绍使用SQLLoader通过编辑控制文件过滤掉源flat数据文件中不需要加载至数据库表中的列的方法。具体演示了如何从四列源数据中移除一列，并成功加载至只有三列的数据库表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一描述

此档是sqlloader学习渐进的第三篇.目的编辑控制文件过滤掉源flat数据文件中不需要加载进数据库表中的列.如示例数据中为4列,数据库表中为3列,将源flat数据中指定的一列去掉后再加载进数据库表中.

二操作环境

OS info

windows server 2003 32bit

DB info

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 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_case3

三过程设计

1.准备的flat源数据.

20,Something Not To Be Loaded,Accounting,"Virginia,USA"

(源flat数据为4列,以','分隔)

2.相关文件ppt_case3.sql

rem
rem
rem

set termout off

drop table dept_p3;
create table dept_p3 (deptno number(2),dname char(20),loc char(20));
exit;

3.相关文件ppt_case3.ctl

LOAD DATA
INFILE *
INTO TABLE DEPT_P3
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( DEPTNO,
FILLER_1 FILLER,
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,"Virginia,USA"

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

四详细步骤操作

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

SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 15:31:12 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_case3
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_case3>

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

SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 15:33:51 2012

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

Commit point reached - logical record count 1

C:\sqlloader_exec\ppt_case3>

3.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on Sun Aug 5 15:33:51 2012

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

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

   Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   , O(") CHARACTER
FILLER_1                             NEXT     *   , O(") CHARACTER
(FILLER FIELD)
DNAME                                NEXT     *   , O(") CHARACTER
LOC                                  NEXT     *   , O(") CHARACTER

Table DEPT_P3:
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:                  49536 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 15:33:51 2012
Run ended on Sun Aug 05 15:33:51 2012

Elapsed time was:     00:00:00.82
CPU time was:         00:00:00.00

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

SQL*Plus: Release 9.0.1.0.1 - Production on Sun Aug 5 15:40:38 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_p3;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        20 Accounting           Virginia,USA

SQL>