SQLLOADER 10 载入有换行符的数据

最新推荐文章于 2023-05-06 17:37:14 发布

cnk45946

最新推荐文章于 2023-05-06 17:37:14 发布

阅读量1k

点赞数

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

本文介绍使用SQLLoader处理含有换行符的数据文件，并通过replace函数实现换行符的转换，确保数据正确加载到Oracle数据库。

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

一描述

此档是sqlloader学习的第10篇.载入有换行符的数据.目的将flat源数据文件中相应的内容使用replace函数替换成相应的换行符.

二操作环境

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:\sqlloader_exec\ppt_case10

三过程设计

1.准备的flat源数据.
10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia

(注:)

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

set termout off

drop table dept_p10;
create table dept_p10 (deptno number(2),dname char(20),loc char(20),last_updated date,comments varchar2(100));
exit;

3.相关文件ppt_case10.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_P10
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS "replace(:comments,'n',chr(10))" --replace 的使用帮助转换换行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia

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

四详细步骤操作

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

SQL*Plus: Release 9.0.1.0.1 - Production on 星期一 8月 6 21:33:36 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> @ppt_case10
从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_case10>

2.在cmd命令下执行sqlldr加载ppt_case10.ctl控制文件命令
C:\sqlloader_exec\ppt_case10>set nls_lang=AMERICAN_AMERICA.zhs16gbk

C:\sqlloader_exec\ppt_case10>sqlldr userid=scott/tiger control=ppt_case10.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on Mon Aug 6 21:35:11 2012

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

Commit point reached - logical record count 4

C:\sqlloader_exec\ppt_case10>

3.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on Mon Aug 6 21:35:11 2012

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

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

   Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
    SQL string for column : "upper(:dname)"
LOC                                  NEXT     *   ,       CHARACTER
    SQL string for column : "upper(:loc)"
LAST_UPDATED                         NEXT     *   ,       CHARACTER
    SQL string for column : "my_to_date( :last_updated )"
COMMENTS                             NEXT     *   ,       CHARACTER
    SQL string for column : "replace(:comments,'n',chr(10))"

Table DEPT_P10:
4 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:                  82560 bytes(64 rows)
Read   buffer bytes: 1048576

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

Run began on Mon Aug 06 21:35:11 2012
Run ended on Mon Aug 06 21:35:12 2012

Elapsed time was:     00:00:00.89
CPU time was:         00:00:00.01

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

SQL*Plus: Release 9.0.1.0.1 - Production on Mon Aug 6 21:37:29 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> col comments for a30
SQL> select * from dept_p10;

    DEPTNO DNAME                LOC                  LAST_UPDA COMMENTS
---------- -------------------- -------------------- --------- ------------------------------
        10 SALES                VIRGINIA             01-APR-01 This is the Sales
                                                               Office i
                                                                Virgi
                                                               ia

        20 ACCOUNTING           VIRGINIA             13-APR-01 This is the Accou
                                                               ti
                                                               g
                                                               Office i
                                                                Virgi
                                                               ia

    DEPTNO DNAME                LOC                  LAST_UPDA COMMENTS
---------- -------------------- -------------------- --------- ------------------------------

        30 CONSULTING           VIRGINIA             14-APR-01 This is the Co
                                                               sulti
                                                               g
                                                               Office i
                                                                Virgi
                                                               ia

        40 FINANCE              VIRGINIA             14-APR-01 This is the Fi
                                                               a
                                                               ce

    DEPTNO DNAME                LOC                  LAST_UPDA COMMENTS
---------- -------------------- -------------------- --------- ------------------------------
                                                               Office i
                                                                Virgi
                                                               ia

SQL>