SQLLDR Case Study 11: Loading Data in the Unicode Character Set-CSDN博客

一描述

Case Study 11: Loading Data in the Unicode Character Set: Loads data in the Unicode character set, UTF16, in little endian byte order. This case study uses character-length semantics.

In this case study, SQL*Loader loads data from a datafile in a Unicode character set. This case study parallels case study 3, except that it uses the character set UTF16 and a maximum length is specified for the empno and deptno fields. The data must be in a separate datafile because the CHARACTERSET keyword is specified.This case study demonstrates the following:

Using SQL*Loader to load data in the Unicode character set, UTF16.
Using SQL*Loader to load data in a fixed-width multibyte character set.
Using character-length semantics.
Using SQL*Loader to load data in little endian byte order. SQL*Loader checks the byte order of the system on which it is running. If necessary, SQL*Loader swaps the byte order of the data to ensure that any byte-order-dependent data is correctly loaded.

二操作环境

OS info

windows server2003 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数据
(源数据文件导入不过来)

2.相关文件ulcase11.sql
Rem
Rem $Header: ulcase11.sql 06-feb-2001.14:26:12 rpfau Exp $
Rem
Rem ulcase11.sql
Rem
Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved.
Rem
Rem    NAME
Rem      ulcase11.sql - Set up for SQL Loade example 11
Rem
Rem    DESCRIPTION
Rem      Create table emp for example loading little endian unicode (UTF-16)
Rem    data.
Rem
Rem    NOTES
Rem      None
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rpfau       02/06/01 - Merged rpfau_sqlldr_add_case_study_11
Rem    rpfau       01/30/01 - Created
Rem
set termout off

rem host write sys$output "Building demonstration tables for case study 11. 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),
    projno number,
    loadseq number);

exit

3.相关文件ulcase11.ctl
-- Copyright (c) 2001 by Oracle Corporation
--   NAME
--     ulcase11.ctl - Load Data in the Unicode Character Set UTF-16
--   DESCRIPTION
--     Loads data in the Unicode character set UTF-16. The data is in litle
--     endian byte order. This means that depending on whether SQL*Loader is
--     running on a little endian or a big endian system, it will have to
--     byte swap the UTF-16 character data as necessary. This load uses
--     character length semantics, the default for the character set UTF-16.
--
--     This case study is modeled after case study 3 (ulcase3), which loads
--     variable length delimited (terminated and enclosed) data.
--
--   RETURNS
--
--   NOTES
--     None
--   MODIFIED   (MM/DD/YY)
--    rpfau     02/06/01 - Merged rpfau_sqlldr_add_case_study_11
--    rpfau     01/30/01 - Creation
--

LOAD DATA
CHARACTERSET utf16
BYTEORDER little
INFILE ulcase11.dat
REPLACE

INTO TABLE EMP
FIELDS TERMINATED BY X'002c' OPTIONALLY ENCLOSED BY X'0022'
(empno integer external (5), ename, job, mgr,
hiredate DATE(20) "DD-Month-YYYY",
sal, comm,
deptno   CHAR(5) TERMINATED BY ":",
projno,
loadseq SEQUENCE(MAX,1) )

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

四详细步骤操作

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 Wed Aug 8 13:05:50 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>

2.scott用户运行ulcase11.sql初始化环境
SQL> @ulcase11
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:\oracle\product\9.2.0\db_1\rdbms\demo>

3.在cmd命令下执行sqlldr加载ulcase11.ctl控制文件命令
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr "scott/tiger" control=ulcase11.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on Wed Aug 8 13:07:11 2012

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

Commit point reached - logical record count 7

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

4.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on Wed Aug 8 13:07:11 2012

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

Control File:   ulcase11.ctl
Character Set utf16 specified for all input.
Using character length semantics.
Byteorder little endian specified.

Data File:      ulcase11.dat
Bad File:     ulcase11.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 EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO                               FIRST    10   , O(") CHARACTER
ENAME                                NEXT     *   , O(") CHARACTER
JOB                                  NEXT     *   , O(") CHARACTER
MGR                                  NEXT     *   , O(") CHARACTER
HIREDATE                             NEXT    40   , O(") DATE DD-Month-YYYY
SAL                                  NEXT     *   , O(") CHARACTER
COMM                                 NEXT     *   , O(") CHARACTER
DEPTNO                               NEXT    10   : O(") CHARACTER
PROJNO                               NEXT     *   , O(") CHARACTER
LOADSEQ                                                   SEQUENCE (MAX, 1)

Table EMP:
7 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:                 104768 bytes(64 rows)
Read   buffer bytes: 1048576

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

Run began on Wed Aug 08 13:07:11 2012
Run ended on Wed Aug 08 13:07:12 2012

Elapsed time was:     00:00:00.84
CPU time was:         00:00:00.02

5.查看sqlldr bad文件信息
(注:无)
6.查看数据库加载成功的数据
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on Wed Aug 8 13:08:10 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 120
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO     PROJNO    LOADSEQ
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7782 Clark      Manager         7839 09-JUN-81     2572.5                    10        101       1
      7839 King       President            17-NOV-81       5500                    10        102       2
      7934 Miller     Clerk           7782 23-JAN-82        920                    10        102       3
      7566 Jones      Manager         7839 02-APR-81    3123.75                    20        101       4
      7499 Allen      Salesman        7698 20-FEB-81       1600        300         30        103       5
      7654 Martin     Salesman        7698 28-SEP-81     1312.5       1400         30        103       6
      7658 Chan       Analyst         7566 03-MAY-82       3450                    20        101       7

7 rows selected.

SQL>