J****u
发帖数: 15 | 1 Hello, all. I need your help. I am running a query using sqlplus and
get error ORA-01722. It means that error happens when it converts
non-numeric characters into numeric format.
I want to know at what point it hits the error so that I can find out
which non-numeric character is causing the problem and fix it.
Do you know any debugging or other methods to figure that out?
Thanks.
John | x********o
发帖数: 31 | 2 it should stop at the error line.
if you can post your SQLplus on BBS, maybe i can help you find the bugs.
Usually, i found the bugs with ZHI Jue.
【在 J****u 的大作中提到】
: Hello, all. I need your help. I am running a query using sqlplus and
: get error ORA-01722. It means that error happens when it converts
: non-numeric characters into numeric format.
: I want to know at what point it hits the error so that I can find out
: which non-numeric character is causing the problem and fix it.
: Do you know any debugging or other methods to figure that out?
: Thanks.
: John
| J****u
发帖数: 15 | 3 The query is like:
insert into target
select total from source;
The total field in table source is a char(10) variable,
and it is a number field in table target.
Even I put to_number and change the query to:
insert into target select to_number(total) from source;
It still does not work.
The reason I think maybe is the total field in source has non-numeric
value. But I don't know how to prove it.
If I change the query to
insert into target select length(total) from source;
It works perfect and pu
【在 x********o 的大作中提到】
: it should stop at the error line.
: if you can post your SQLplus on BBS, maybe i can help you find the bugs.
: Usually, i found the bugs with ZHI Jue.
| x********o
发帖数: 31 | 4 is there $ sign in the total field in the source?
try
select instr(total,'$') from source;
if the number is not 0, it means that the total is not pure number char.
【在 J****u 的大作中提到】
: The query is like:
: insert into target
: select total from source;
: The total field in table source is a char(10) variable,
: and it is a number field in table target.
: Even I put to_number and change the query to:
: insert into target select to_number(total) from source;
: It still does not work.
: The reason I think maybe is the total field in source has non-numeric
: value. But I don't know how to prove it.
| J****u
发帖数: 15 | 5 I tried, but failed. The problem is the total field
can contain anything other than pure number. It can contain a, b,c
,d,.... I can not try it one by one anyhow. And the table has 300,000
records, which increase difficulty. Any other good idea.
【在 x********o 的大作中提到】
: is there $ sign in the total field in the source?
: try
: select instr(total,'$') from source;
: if the number is not 0, it means that the total is not pure number char.
| x********o
发帖数: 31 | 6 it doesnot make sense. to my experience, a total field should contain no letters.
if there is a lot of letters in the field, why do you want to convert to
pure number?
is the field like a8990.00b? or a8b90.00?
【在 J****u 的大作中提到】
: I tried, but failed. The problem is the total field
: can contain anything other than pure number. It can contain a, b,c
: ,d,.... I can not try it one by one anyhow. And the table has 300,000
: records, which increase difficulty. Any other good idea.
|
|
