|
用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) [1] [2] [3] 下一页 |
