开源中文网

您的位置: 首页 > Oracle > 正文

用sqlldr从mysql导出一个表的数据到oracle

来源:  作者:

用sqlldr从mysql导出一个表的数据到oracle


代码:--------------------------------------------------------------------------------
1 进入mysql
mysql> select UUID, CYC_CODE, b03_05, b03_06, b03_07, b03_08, b03_09, b03_10, b03_11, b03_12, Cate_Item_Code, product_name, product_unit 
from  jb603_jb603_sub3 into outfile ''''d:/tmp/603sub.txt'''';

Query OK, 79537 rows affected (0.48 sec)
mysql> show create table jb603_jb603_sub3;

| jb603_jb603_sub3 | CREATE TABLE `jb603_jb603_sub3` (
  `UUID` varchar(32) NOT NULL default '''''''',
  `CYC_CODE` char(2) NOT NULL default '''''''',
  `b03_05` decimal(11,2) default ''''0.00'''',
  `b03_06` decimal(11,2) default ''''0.00'''',
  `b03_07` decimal(11,2) default ''''0.00'''',
  `b03_08` decimal(11,2) default ''''0.00'''',
  `b03_09` decimal(9,0) default ''''0'''',
  `b03_10` decimal(11,2) default ''''0.00'''',
  `b03_11` decimal(11,2) default ''''0.00'''',
  `b03_12` decimal(11,2) default ''''0.00'''',
  `Cate_Item_Code` varchar(7) NOT NULL default '''''''',
  `product_name` varchar(60) default '''''''',
  `product_unit` varchar(20) default '''''''',
  PRIMARY KEY  (`UUID`,`CYC_CODE`,`Cate_Item_Code`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
将语句整理成oracle支持的格式:
 CREATE TABLE jb603_jb603_sub3 (
  UUID varchar(32) NOT NULL ,
  CYC_CODE char(2) NOT NULL ,
  b03_05 number(11,2) ,
  b03_06 number(11,2) ,
  b03_07 number(11,2) ,
  b03_08 number(11,2) ,
  b03_09 number(9,0) ,
  b03_10 number(11,2) ,
  b03_11 number(11,2) ,
  b03_12 number(11,2) ,
  Cate_Item_Code varchar(7) NOT NULL ,
  product_name varchar(60) ,
  product_unit varchar(20) ,
  PRIMARY KEY  (UUID,CYC_CODE,Cate_Item_Code));
2 编写sqlldr控制文件t.ctl
load data
infile ''''d:/tmp/603sub.txt''''
into table   jb603_jb603_sub3
replace
fields terminated by x''''09''''
(UUID, CYC_CODE, b03_05, b03_06, b03_07, b03_08, b03_09, b03_10, b03_11, b03_12, Cate_Item_Code, product_name, product_unit)
3运行sqlplus /nolog
SQL> conn 
lt/lt5@ibmlt
建立jb603_jb603_sub3表
SQL>  CREATE TABLE jb603_jb603_sub3 (
  2    UUID varchar(32) NOT NULL ,
  3    CYC_CODE char(2) NOT NULL ,
  4    b03_05 number(11,2) ,
  5    b03_06 number(11,2) ,
  6    b03_07 number(11,2) ,
  7    b03_08 number(11,2) ,
  8    b03_09 number(9,0) ,
  9    b03_10 number(11,2) ,
 10    b03_11 number(11,2) ,
 11    b03_12 number(11,2) ,
 12    Cate_Item_Code varchar(7) NOT NULL ,
 13    product_name varchar(60) ,
 14    product_unit varchar(20) ,
 15    PRIMARY KEY  (UUID,CYC_CODE,Cate_Item_Code));

表已创建。
4 ho进入操作系统命令行
在操作系统命令行运行sqlldr 
lt/lt_5@ibmlt d:/tmp/t.ctl
....
达到提交点,逻辑记录计数79537
5 exit回到sqlplus

SQL> select count(*)from JB603_JB603_SUB3;

  COUNT(*)
----------
     79537

6另外,我不明白为什么用外部表方式不能成功
sqlldr 
lt/lt5@ibmlt d:/tmp/t.ctl external_table=generate_only
产生t.log

SQL*Loader: Release 9.2.0.1.0 - Production on 星期日 3月 26 13:11:41 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

控制文件: d:/tmp/t.ctl
数据文件: d:/tmp/603sub.txt
错误文件: d:/tmp/603sub.bad
废弃文件: 未作指定

(可废弃所有记录)

加载数: ALL
跳过数: 0
允许的错误: 50
继续:    未作指定
所用路径:       外部表

表JB603_JB603_SUB3
已加载从每个逻辑记录
插入选项对此表REPLACE生效

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
UUID                                FIRST     *  WHT      CHARACTER            
CYC_CODE                             NEXT     *  WHT      CHARACTER            
B03_05                               NEXT     *  WHT      CHARACTER            
B03_06                               NEXT     *  WHT      CHARACTER            
B03_07                               NEXT     *  WHT      CHARACTER            
B03_08                               NEXT     *  WHT      CHARACTER            
B03_09                               NEXT     *  WHT      CHARACTER            
B03_10                               NEXT     *  WHT      CHARACTER            
B03_11                               NEXT     *  WHT      CHARACTER            
B03_12                               NEXT     *  WHT      CHARACTER            
CATE_ITEM_CODE                       NEXT     *  WHT      CHARACTER            
PRODUCT_NAME                         NEXT     *  WHT      CHARACTER            
PRODUCT_UNIT                         NEXT     *  WHT      CHARACTER           

 

用于外部表的 CREATE TABLE 语句:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_JB603_JB603_S" 
(
  UUID VARCHAR2(32),
  CYC_CODE CHAR(2),
  B03_05 NUMBER(11,2),
  B03_06 NUMBER(11,2),
  B03_07 NUMBER(11,2),
  B03_08 NUMBER(11,2),
  B03_09 NUMBER(9),
  B03_10 NUMBER(11,2),
  B03_11 NUMBER(11,2),
  B03_12 NUMBER(11,2),
  CATE_ITEM_CODE VARCHAR2(7),
  PRODUCT_NAME VARCHAR2(60),
  PRODUCT_UNIT VARCHAR2(20)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY UTL_FILE_DIR
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE ''''UTL_FILE_DIR'''':''''603sub.bad''''
    LOGFILE ''''t.log_xt''''
    READSIZE 1048576
    FIELDS TERMINATED BY 0x''''09'''' LDRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      UUID CHAR(255)
        TERMINATED BY 0x''''09'''',
      CYC_CODE CHAR(255)
        TERMINATED BY 0x''''09'''',
      B03_05 CHAR(255)
        TERMINATED BY 0x''''09'''',
      B03_06 CHAR(255)
        TERMINATED BY 0x''''09'''',
      B03_07 CHAR(255)
        TERMINATED BY 0x''''09'''',
      B03_08 CHAR(255)
        TERMINATED BY 0x''''09'''',
      B03_09 CHAR(255)
        TERMINATED BY 0x''''09'''',
      B03_10 CHAR(255)
        TERMINATED BY 0x''''09'''',
      B03_11 CHAR(255)
        TERMINATED BY 0x''''09'''',
      B03_12 CHAR(255)
        TERMINATED BY 0x''''09'''',
      CATE_ITEM_CODE CHAR(255)
        TERMINATED BY 0x''''09'''',
      PRODUCT_NAME CHAR(255)
        TERMINATED BY 0x''''09'''',
      PRODUCT_UNIT CHAR(255)
        TERMINATED BY 0x''''09''''
    )
  )
  location 
  (
    ''''603sub.txt''''
  )
)REJECT LIMIT UNLIMITED


用于加载内部表的 INSERT 语句:
------------------------------------------------------------------------
INSERT /*+ append */ INTO JB603_JB603_SUB3 
(
  UUID,
  CYC_CODE,
  B03_05,
  B03_06,
  B03_07,
  B03_08,
  B03_09,
  B03_10,
  B03_11,
  B03_12,
  CATE_ITEM_CODE,
  PRODUCT_NAME,
  PRODUCT_UNIT
)
SELECT 
  UUID,
  CYC_CODE,
  B03_05,
  B03_06,
  B03_07,
  B03_08,
  B03_09,
  B03_10,
  B03_11,
  B03_12,
  CATE_ITEM_CODE,
  PRODUCT_NAME,
  PRODUCT_UNIT
FROM "SYS_SQLLDR_X_EXT_JB603_JB603_S"


用于清除由以前的语句创建的对象的语句:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_JB603_JB603_S"

 

从星期日 3月  26 13:11:41 2006开始运行
在星期日 3月  26 13:11:41 2006处运行结束

经过时间为: 00: 00: 00.14
CPU 时间为: 00: 00: 00.04

我摘出建外部表的语句执行
SQL> CREATE TABLE "SYS_SQLLDR_X_EXT_JB603_JB603_S"
  2  (
  3    UUID VARCHAR2(32),
  4    CYC_CODE CHAR(2),
  5    B03_05 NUMBER(11,2),
  6    B03_06 NUMBER(11,2),
  7    B03_07 NUMBER(11,2),
  8    B03_08 NUMBER(11,2),
  9    B03_09 NUMBER(9),
 10    B03_10 NUMBER(11,2),
 11    B03_11 NUMBER(11,2),
 12    B03_12 NUMBER(11,2),
 13    CATE_ITEM_CODE VARCHAR2(7),
 14    PRODUCT_NAME VARCHAR2(60),
 15    PRODUCT_UNIT VARCHAR2(20)
 16  )
 17  ORGANIZATION external
 18  (
 19    TYPE oracle_loader
 20    DEFAULT DIRECTORY UTL_FILE_DIR
 21    ACCESS PARAMETERS
 22    (
 23      RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
 24      BADFILE ''''UTL_FILE_DIR'''':''''603sub.bad''''
 25      LOGFILE ''''t.log_xt''''
 26      READSIZE 1048576
 27      FIELDS TERMINATED BY 0x''''09'''' LDRTRIM
 28    )
 29    location
 30    (
 31      ''''603sub.txt''''
 32    )
 33  )
 34  /

表已创建。
当603sub.txt记录很少的时候,没问题
SQL> select uuid from  SYS_SQLLDR_X_EXT_JB603_JB603_S;

UUID
--------------------------------
00001B3726AD4276AD661393F92F9108


当603sub.txt记录多的时候

 

SQL> select count(*)from SYS_SQLLDR_X_EXT_JB603_JB603_S;
select count(*)from SYS_SQLLDR_X_EXT_JB603_JB603_S
*
ERROR 位于第 1 行:
ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错
ORA-29400: 数据插件错误KUP-04020: found record longer than buffer size
supported, 1048576, in d:tmp603sub.txt
ORA-06512: 在"SYS.ORACLE_LOADER", line 14
ORA-06512: 在line 1


t.log_xt
 LOG file opened at 03/26/06 12:52:33

Field Definitions for table SYS_SQLLDR_X_EXT_JB603_JB603_S
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:

    UUID                            CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    CYC_CODE                        CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    B03_05                          CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    B03_06                          CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    B03_07                          CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    B03_08                          CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    B03_09                          CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    B03_10                          CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    B03_11                          CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    B03_12                          CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    CATE_ITEM_CODE                  CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    PRODUCT_NAME                    CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
    PRODUCT_UNIT                    CHAR (255)
      Terminated by "09"
      Trim whitespace same as SQL Loader
KUP-04020: found record longer than buffer size supported, 1048576, in d:tmp603sub.txt
KUP-04053: record number 1

 

-------------------------------------------------------------------------------------------

难道是换行符的原因,但是sqlldr是成功的
果然是换行符的问题 
mysql>
select UUID, CYC_CODE, b03_05, b03_06, b03_07, b03_08, b03_09, b03_10, b03_11, b03_12, Cate_Item_Code, product_name, product_unit 
from jb603_jb603_sub3 into outfile ''''d:/tmp/603sub.txt''''LINES TERMINATED BY ''''\r\n'''';

SQL> select count(*)from SYS_SQLLDR_X_EXT_JB603_JB603_S;

COUNT(*)
----------
79537

RECORDS DELIMITED BY 0x''''0A'''' 
SQL> CREATE TABLE "EXT_S"
2 (
3 UUID VARCHAR2(32),
4 CYC_CODE CHAR(2),
5 B03_05 NUMBER(11,2),
6 B03_06 NUMBER(11,2),
7 B03_07 NUMBER(11,2),
8 B03_08 NUMBER(11,2),
9 B03_09 NUMBER(9),
10 B03_10 NUMBER(11,2),
11 B03_11 NUMBER(11,2),
12 B03_12 NUMBER(11,2),
13 CATE_ITEM_CODE VARCHAR2(7),
14 PRODUCT_NAME VARCHAR2(60),
15 PRODUCT_UNIT VARCHAR2(20)
16 )
17 ORGANIZATION external
18 (
19 TYPE oracle_loader
20 DEFAULT DIRECTORY EXT_DATA_DIR
21 ACCESS PARAMETERS
22 (
23 RECORDS DELIMITED BY 0x''''0A'''' CHARACTERSET ZHS16GBK
24 BADFILE ''''UTL_FILE_DIR'''':''''603sub.bad''''
25 LOGFILE ''''t.log_xt''''
26 READSIZE 1048576
27 FIELDS TERMINATED BY 0x''''09'''' LDRTRIM
28 )
29 location
30 (
31 ''''603sub.tx1''''
32 )
33 )
34 /

表已创建。

SQL> select count(*) from ext_s;

COUNT(*)
----------
79537

Tags:sqlldr mysql oracle
关于开源中文网 - 联系我们 - 广告服务 - 网站地图 - 版权声明