用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
版权声明：
原创作品，许诺转载，转载时请务必以超链接方法标明文章 原始因由 、作者信息和本声明。否则将追查法令责任。