SQL*Loader之CASE11

最新推荐文章于 2024-07-02 20:01:30 发布

原创最新推荐文章于 2024-07-02 20:01:30 发布 · 464 阅读

1 ·

CC 4.0 BY-SA版权

本文介绍了一个使用SQL*Loader加载UTF-16编码数据到Oracle数据库的案例，详细展示了SQL脚本、控制文件及数据文件的配置，并解释了关键参数如CHARACTERSET和BYTEORDER的作用。

CASE11

1. SQL脚本

[oracle@node3 ulcase]$ cat ulcase11.sql

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

2. 控制文件

[oracle@node3 ulcase]$ cat ulcase11.ctl

-- Copyright (c) 1991, 2004 Oracle.  All rights reserved.
-- NAME
-- ulcase11.ctl - SQL*Loader Case Study 11: Load Data in the Unicode
--                Character Set UTF-16
--
-- DESCRIPTION
-- 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.
--
-- TO RUN THIS CASE STUDY:
-- 1. Before executing this control file, log in to SQL*Plus as
--    scott/tiger. Enter @ulcase11 to execute the SQL script for
--    this case study. This prepares and populates tables and
--    then returns you to the system prompt.
--      
-- 2. At the system prompt, invoke the case study as follows:
-- sqlldr USERID=scott/tiger CONTROL=ulcase11.ctl LOG=ulcase11.log
-- 
-- NOTES ABOUT THIS CONTROL FILE
--
-- The character set specified with the CHARACTERSET keyword is UTF16. 
-- SQL*Loader will convert the data from the UTF16 character set to 
-- the database character set. Because UTF16 is specified as the 
-- character set, character-length semantics are used for the load.
--
-- BYTEORDER LITTLE tells SQL*Loader that the data in the datafile is 
-- in little-endian byte order. SQL*Loader checks the byte order of the 
-- system on which it is running to determine if any byte-swapping is 
-- necessary. In this example, all the character data in UTF16 is 
-- byte-order dependent.
--
-- The TERMINATED BY and OPTIONALLY ENCLOSED BY clauses both specify 
-- hexadecimal strings. The X'002c' is the encoding for a comma (,) in 
-- UTF-16 big-endian format. The X'0022' is the encoding for a double 
-- quotation mark (") in big-endian format. Because the datafile is in 
-- little-endian format, SQL*Loader swaps the bytes before checking for 
-- a match. If these clauses were specified as character strings instead 
-- of hexadecimal strings, SQL*Loader would convert the strings to the 
-- datafile character set (UTF16) and byte-swap as needed before checking 
-- for a match.
--
-- Because character-length semantics are used, the maximum length for 
-- the empno, hiredate, and deptno fields is interpreted as characters, 
-- not bytes.
--
-- The TERMINATED BY clause for the deptno field is specified using the 
-- character string ":". SQL*Loader converts the string to the datafile 
-- character set (UTF16) and byte-swaps as needed before checking for a match.

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

3. 数据文件

数据文件因为是UTF16编码，在文本文件中显示为乱码，在这里不贴出。

执行后结果：

[oracle@node3 ulcase]$ sqlplus scott/tiger @ulcase11.sql

[oracle@node3 ulcase]$ sqlldr userid=scott/tiger control=ulcase11.ctl

SQL> select * from emp;

EMPNO ENAME  JOB       MGR HIREDATE       SAL    COMM  DEPTNO PROJNO    LOADSEQ
----- ------ --------- ----- --------- -------   ----- ------ ------ ----------
 7782 Clark  Manager   7839 09-JUN-81      2573          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      3124          20    101        4 

 7499 Allen  Salesman  7698 20-FEB-81      1600    300   30    103        5

 7654 Martin Salesman  7698 28-SEP-81      1313    1400  30    103        6

 7658 Chan   Analyst   7566 03-MAY-82      3450          20    101        7

7 rows selected.

查看一下日志文件：

[oracle@node3 ulcase]$ cat ulcase11.log

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Sep 19 04:13:59 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  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 Fri Sep 19 04:13:59 2014
Run ended on Fri Sep 19 04:13:59 2014

Elapsed time was:     00:00:00.41
CPU time was:         00:00:00.24