我使用的是 sqlldr 向orcale导入*.txt 数据,现在我使用这个命令以经执行成功了,
C:\Users\ThinkPad>sqlldr userid=ysbase/123123@YSBASE control=e:/append.ctl errors=5000 rows=1000000000
在根目录下也以经生成了 *.bad 文件了,但是我登录数据库查询了一下 居然没有任何的记录晕了,,,,大虾帮帮忙啊,,,,在问一句 *.bad 这个文件是不是相当于一个 是志文件啊。?????
C:\Users\ThinkPad>sqlldr userid=ysbase/123123@YSBASE control=e:/append.ctl errors=5000 rows=1000000000
在根目录下也以经生成了 *.bad 文件了,但是我登录数据库查询了一下 居然没有任何的记录晕了,,,,大虾帮帮忙啊,,,,在问一句 *.bad 这个文件是不是相当于一个 是志文件啊。?????
解决方案 »
- oralce 10g数据库有dbf、ctl、log文件怎么恢复
- oracle 表分析 计算方式
- Oracle SQL查询语句统计数据的问题
- 在SQLSERVER中的视图转ORALCE问题.
- oracle异常处理的问题
- 用户自定义函数移植问题(从sql server到oracle)
- 我想在ORACLE数据库中查询日期小于等于某一日期的记录,但查询不到日期边界值的记录,请帮忙!
- Enterprise Manager Console创建的序列如何与表中一字段绑定起来????
- ORA-00604: error occurred at recursive SQL level 3 救急了!!!
- 请教关于SQL LOADER的用法
- 新手提问 存储过程 游标
- oracle数据库迁移的问题
晕,,那我 的txt和bad 文件 输出的是一样的,那这是都 错啦,,晕了
还有导入到的表的建表SQL语句上传一下。
这样可以实际测试一下。
load data
infile 'e:/123.csv'
insert into table DEFINE_GROUP_SET
fields terminated by ''
(GROUPTYPECODE,
GROUPCODE,
GROUPNAME,
GRADE,
DETAIL,
NOTE,
ID,
CREATOR_ID,
CREATE_TIME "to_timestamp(:CREATE_TIME,'yyyy-MM-dd hh24:mi:ss.ff3')",
COSTCENTERCODE,
BUSINESSTYPECODE,
PARAM01,
TABLECOLUMN,
PARMA02,
PARMA03,
PARAM04,
PARAM05,
GROUPTABLE,
USERTABLE,
RPTCOLWDITH,
RPTROWTOTAL,
RPTCOLTOTAL,
SUBWHERE,
RPTCOLSUM,
GROUPROOTCODE,
REALCOLUMNNAME01,
REALCOLUMNNAME02,
REALCOLUMNNAME03,
REALCOLUMNNAME04,
REALCOLUMNNAME05,
REALCOLUMNCODE01,
REALCOLUMNCODE02,
REALCOLUMNCODE03,
REALCOLUMNCODE04,
REALCOLUMNCODE05,
BUDGETCOLUMNNAME01,
BUDGETCOLUMNNAME02,
BUDGETCOLUMNNAME03,
BUDGETCOLUMNNAME04,
BUDGETCOLUMNNAME05,
BUDGETCOLUMNCODE01,
BUDGETCOLUMNCODE02,
BUDGETCOLUMNCODE03,
BUDGETCOLUMNCODE04,
BUDGETCOLUMNCODE05,
ITEMCODE01,
ITEMCODE02,
ITEMCODE03,
ITEMCODE04,
ITEMCODE05,
PREFIXCONSTANTCOLUMN01,
INFIXCONSTANTCOLUMN01,
SUFFIXCONSTANTCOLUMN01,
SUFFIXCONSTANTCOLUMN02,
RELAMODEL,
DATASOUCE,
ISESTIMATE
)
123.txt
000002 0101 产量商品量 2 0 5000000000000251 9999 2011/1/1 00:00:00 10111200 0 0 100 0 0 01 0 0
000002 0102 收入 2 0 5000000000000252 9999 2011/1/1 00:00:00 10111200 0 1 100 0 0 01 0 0
000002 0103 营业成本 2 0 5000000000000253 9999 2011/1/1 00:00:00 10111200 0 1 100 0 0 01 0 0C:\Users\ThinkPad>sqlldr userid=ysbase/123123@YSBASE control=e:/append.ctl error
s=5000 rows=1000000000
insert into table DEFINE_GROUP_SET
fields terminated by ' ' -- 单引号之间加个空格
还是不行啊,,我把 txt文档就留一行数据,
000002,0103,营业成本,2,0,5000000000000253,9999,2011/1/1 00:00:00,10111200,0,1,100,0,0 01,0,0
然后ctl我改成
infile 'e:/123.csv' -- e:/123.txt
insert into table DEFINE_GROUP_SET
fields terminated by ','
也不行啊
改成e:/123.txt你的数据源文件到底是csv文件还是txt文件?
这个改了,,改了,txt的e:/123.txt
GROUPCODE varchar2 (30) null,
GROUPNAME varchar2 (60) null,
GRADE varchar2 (1) null,
DETAIL varchar2 (1) null,
NOTE varchar2 (100) null,
ID varchar2 (36) not null,
CREATOR_ID numeric null,
CREATE_TIME data null,
COSTCENTERCODE varchar2 (30) null,
BUSINESSTYPECODE varchar2 (30) null,
PARAM01 varchar2 (1) null,
TABLECOLUMN varchar2 (40) null,
PARMA02 varchar2 (200) null,
PARMA03 varchar2 (200) null,
PARAM04 varchar2 (40) null,
PARAM05 varchar2 (1) null,
GROUPTABLE varchar2 (40) null,
USERTABLE varchar2 (40) null,
RPTCOLWDITH varchar2 (4) null,
RPTROWTOTAL varchar2 (1) null,
RPTCOLTOTAL varchar2 (1) null,
SUBWHERE varchar2 (600) null,
RPTCOLSUM varchar2 (1) not null,
GROUPROOTCODE varchar2 (30) null,
REALCOLUMNNAME01 varchar2 (50) null,
REALCOLUMNNAME02 varchar2 (50) null,
REALCOLUMNNAME03 varchar2 (50) null,
REALCOLUMNNAME04 varchar2 (50) null,
REALCOLUMNNAME05 varchar2 (50) null,
REALCOLUMNCODE01 varchar2 (50) null,
REALCOLUMNCODE02 varchar2 (50) null,
REALCOLUMNCODE03 varchar2 (50) null,
REALCOLUMNCODE04 varchar2 (50) null,
REALCOLUMNCODE05 varchar2 (50) null,
BUDGETCOLUMNNAME01 varchar2 (50) null,
BUDGETCOLUMNNAME02 varchar2 (50) null,
BUDGETCOLUMNNAME03 varchar2 (50) null,
BUDGETCOLUMNNAME04 varchar2 (50) null,
BUDGETCOLUMNNAME05 varchar2 (50) null,
BUDGETCOLUMNCODE01 varchar2 (50) null,
BUDGETCOLUMNCODE02 varchar2 (50) null,
BUDGETCOLUMNCODE03 varchar2 (50) null,
BUDGETCOLUMNCODE04 varchar2 (50) null,
BUDGETCOLUMNCODE05 varchar2 (50) null,
ITEMCODE01 varchar2 (50) null,
ITEMCODE02 varchar2 (50) null,
ITEMCODE03 varchar2 (50) null,
ITEMCODE04 varchar2 (50) null,
ITEMCODE05 varchar2 (50) null,
PREFIXCONSTANTCOLUMN01 varchar2 (30) null,
INFIXCONSTANTCOLUMN01 varchar2 (30) null,
SUFFIXCONSTANTCOLUMN01 varchar (30) null,
SUFFIXCONSTANTCOLUMN02 varchar (30) null,
RELAMODEL varchar2 (1) not null,
DATASOUCE varchar2 (1) null,
ISESTIMATE varchar2 (1) null)
CREATE_TIME data null,应该为:
CREATE_TIME DATE null,
导出的日志是空的,晕C:\Users\ThinkPad>sqlldr userid=ysbase/123123@YSBASE control=e:/append.ctl error
s=5000 rows=1000000000 data=e:\test.log
我看了是对的,,DATA 的数据类型,在PLSQL看的,,这个不会有错吧
data是啥类型啊。
刘哥加油 ,我要回去了 11点寝室关门
lz好运
明早再来看 这个我做过实验的
123.txt 数据是有的,后面的都 是空的,
那我估计就是 我和txt的格式的问题 了,谢谢啦,,我在看看
但是在 数据库里面就是 没有这条记录,晕死了
txt
1,2,3,4,5,6,7,8,2012-01-01 12:00:00.00,10,11,12,13,14,15,16,17,18,19,20,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22
ctl
load data
infile 'e:/456.txt'
insert into table DEFINE_GROUP_SET
fields terminated by ','
(GROUPTYPECODE,
GROUPCODE,
GROUPNAME,
GRADE,
DETAIL,
NOTE,
ID,
CREATOR_ID,
CREATE_TIME "to_timestamp(:CREATE_TIME,'yyyy-MM-dd hh24:mi:ss.ff3')",
COSTCENTERCODE,
BUSINESSTYPECODE,
PARAM01,
TABLECOLUMN,
PARMA02,
PARMA03,
PARAM04,
PARAM05,
GROUPTABLE,
USERTABLE,
RPTCOLWDITH,
RPTROWTOTAL,
RPTCOLTOTAL,
SUBWHERE,
RPTCOLSUM,
GROUPROOTCODE,
REALCOLUMNNAME01,
REALCOLUMNNAME02,
REALCOLUMNNAME03,
REALCOLUMNNAME04,
REALCOLUMNNAME05,
REALCOLUMNCODE01,
REALCOLUMNCODE02,
REALCOLUMNCODE03,
REALCOLUMNCODE04,
REALCOLUMNCODE05,
BUDGETCOLUMNNAME01,
BUDGETCOLUMNNAME02,
BUDGETCOLUMNNAME03,
BUDGETCOLUMNNAME04,
BUDGETCOLUMNNAME05,
BUDGETCOLUMNCODE01,
BUDGETCOLUMNCODE02,
BUDGETCOLUMNCODE03,
BUDGETCOLUMNCODE04,
BUDGETCOLUMNCODE05,
ITEMCODE01,
ITEMCODE02,
ITEMCODE03,
ITEMCODE04,
ITEMCODE05,
PREFIXCONSTANTCOLUMN01,
INFIXCONSTANTCOLUMN01,
SUFFIXCONSTANTCOLUMN01,
SUFFIXCONSTANTCOLUMN02,
RELAMODEL,
DATASOUCE,
ISESTIMATE
)
实测的ctl文件,已经修改成我的实际位置load data
infile 'f:\123.txt'
insert into table DEFINE_GROUP_SET
fields terminated by ','
(
GROUPTYPECODE,
GROUPCODE,
GROUPNAME
)
123.txt000002,0101,产量商品量
GROUPTYPECODE char (6) null,
GROUPCODE varchar2 (30) null,
GROUPNAME varchar2 (60) NULL);
原因就是格式不对。
1,2,3,4,5,6,7,8,2012-01-01 12:00:00.00,10,11,12,13,14,15,16,17,18,19,20,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22,22
1,2,3,4,5,6,7,8,2012-01-01 12:00:00.00,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2然后就有了 如下(格式难看了点):
SQL> host sqlldr scott/tiger control='c:\append.ctl';SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 2月 22 09:27:32 2012Copyright (c) 1982, 2005, Oracle. All rights reserved.达到提交点 - 逻辑记录计数 1SQL> select * from DEFINE_GROUP_SET;GROUPT GROUPCODE GROUPNAME
G
------ ------------------------------ ------------------------------------------
------------------ -
D
-
NOTE
--------------------------------------------------------------------------------
--------------------
ID CREATOR_ID CREATE_TIME COSTCENTERCODE
------------------------------------ ---------- -------------- -----------------
-------------
BUSINESSTYPECODE P TABLECOLUMN
------------------------------ - ----------------------------------------
PARMA02
--------------------------------------------------------------------------------
--------------------
PARMA03
--------------------------------------------------------------------------------
--------------------
PARAM04 P GROUPTABLE
---------------------------------------- - -------------------------------------
---
USERTABLE RPTC R R
---------------------------------------- ---- - -
SUBWHERE
--------------------------------------------------------------------------------
--------------------
R GROUPROOTCODE REALCOLUMNNAME01
- ------------------------------ -----------------------------------------------
---
REALCOLUMNNAME02
--------------------------------------------------
REALCOLUMNNAME03
--------------------------------------------------
REALCOLUMNNAME04
--------------------------------------------------
REALCOLUMNNAME05
--------------------------------------------------
REALCOLUMNCODE01
--------------------------------------------------
REALCOLUMNCODE02
--------------------------------------------------
REALCOLUMNCODE03
--------------------------------------------------
REALCOLUMNCODE04
--------------------------------------------------
REALCOLUMNCODE05
--------------------------------------------------
BUDGETCOLUMNNAME01
--------------------------------------------------
BUDGETCOLUMNNAME02
--------------------------------------------------
BUDGETCOLUMNNAME03
--------------------------------------------------
BUDGETCOLUMNNAME04
--------------------------------------------------
BUDGETCOLUMNNAME05
--------------------------------------------------
BUDGETCOLUMNCODE01
--------------------------------------------------
BUDGETCOLUMNCODE02
--------------------------------------------------
BUDGETCOLUMNCODE03
--------------------------------------------------
BUDGETCOLUMNCODE04
--------------------------------------------------
BUDGETCOLUMNCODE05
--------------------------------------------------
ITEMCODE01
--------------------------------------------------
ITEMCODE02
--------------------------------------------------
ITEMCODE03
--------------------------------------------------
ITEMCODE04
--------------------------------------------------
ITEMCODE05 PREFIXCONSTANTCOLUMN01
-------------------------------------------------- -----------------------------
-
INFIXCONSTANTCOLUMN01 SUFFIXCONSTANTCOLUMN01 SUFFIXCONSTANTCOLU
MN02 R D I
------------------------------ ------------------------------ ------------------
------------ - - -
1 2 3
4
5
6
7 8 01-1月 -12 1
1 1 1
1
1
1 1 1
1 2 2 2
2
2 2 2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2 2
2 2 2
2 2 2
SQL>