This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / 关于SQLLDR中出现的问题
-polareskimo(Inuit);
2005-6-11
{788}
(#2340824@0)
-
再扑!!!!!!!!!!!!
-polareskimo(Inuit);
2005-6-13
(#2344351@0)
-
我试了一下这样可以的，主要to_number的使用winxp/oracle9i:
表：
create table SCOTT.A
(
NAME VARCHAR2(10),
AC NUMBER(20,2)
)
控制文件:
Load data
into table A
Append
fields terminated by ","
(
Name,
AC "to_number(:AC,'999999999.99')"
)
数据文件：
zf,000000023.45
df,000000234.12
-markriver(markriver);
2005-6-13
{270}
(#2344816@0)
-
still didn't work....it is not the simple problem about the function....I can't load any number with decimal from a plain txt file....
-polareskimo(Inuit);
2005-6-13
(#2344843@0)
-
你用我提供的这些文件，在你机器上执行也通不过？？？
我是在我机器上试成功的
-markriver(markriver);
2005-6-13
(#2344864@0)
-
unluckly yes.....must be something wrong with Oracle ddl, but i don't know where is it and how to fix it....
-polareskimo(Inuit);
2005-6-13
(#2344912@0)
-
你试试以字符串的方式load到另一个表中，然后写个sp insert倒你的表中，在sp里用to_number转换类型。
-hard20(hard20);
2005-6-14
(#2345099@0)
-
crazy....I created a table, T_CHAR, almost same as T except column A is defined as vchar2.
sqlldr was successful when loading data from the text file to this table.
then I used this script:
insert into t select ..., to_number(A) , ... from t_char;
unfortunately, same error message appeared: ORA-01722, invalid number.....
I am so mad about this stupid error....
-polareskimo(Inuit);
2005-6-14
{366}
(#2346865@0)
-
我上面不是说换换to_number的用法吗？你没试？
换成这样应该可以to_number(:AC,'999999999.99')
-markriver(markriver);
2005-6-14
(#2346888@0)
-
试完了请回个结果贴，我也在这纳闷好几天了.呵呵
-markriver(markriver);
2005-6-14
(#2346897@0)
-
如果是这样的话，我觉得问题不是出在SQLLDR，使出现在你的数据中，你应该检查一下你的数据是否又出现小数点后多于两位的坏数据在里面。
-hard20(hard20);
2005-6-15
(#2347075@0)
-
数据绝对没错, 因为在其他的机器上试过, 不用TO_NUMBER都可以的...
-polareskimo(Inuit);
2005-6-15
(#2348884@0)
-
呵呵。。。我就是试过了呀。。。肯定不是一个函数那么简单的问题。。。
-polareskimo(Inuit);
2005-6-15
(#2348881@0)
-
你的crazy...这个帖子里，to_number显然没有用
to_number(A,'999999999.99')吗？ 试了也不行？？？这也太怪了...
-markriver(markriver);
2005-6-15
(#2348982@0)
-
你是试这样把 to_number(trim(a))
-hard20(hard20);
2005-6-16
(#2349187@0)
-
Thanks, all nice guys here. I worked it out.the trick is i should put enough '9' in the format expression coz my raw data are quite long...So, control file works perfectly now.
BTW, i did use '9' in to_number function, though it was not as many as the system wanted.
-polareskimo(Inuit);
2005-6-16
{229}
(#2350363@0)
-
有始有终，赞一个
-hard20(hard20);
2005-6-16
(#2350886@0)
-
thanks. i'm also very happy about that. :-)
-polareskimo(Inuit);
2005-6-16
(#2350900@0)