• 用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`)
    ) TYPE=InnoDB |
    将语句料理整理成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 'rn';

    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




    版权声明: 原创作品,许诺转载,转载时请务必以超链接方法标明文章 原始因由 、作者信息和本声明。否则将追查法令责任。

  • 相关阅读:
    基础知识记录
    不同数据库'查询第几行到第几行记录'脚本的区别
    visual studio自动生成的私有内部字段变量以_为前缀
    ASP.NET Core 集成Prometheus+grafana
    netcore命令行运行程序
    RabbitMQ使用建议
    解决Pg新增数据主键冲突
    .Net Core调用第三方WebService
    .Net Core调用oracle存储过程
    call,apply,bind使用区别
  • 原文地址:https://www.cnblogs.com/zgqjymx/p/1975831.html
Copyright © 2020-2023  润新知