Redian新闻
>
Debugging in SQLPLUS, need help
avatar
Debugging in SQLPLUS, need help# Database - 数据库
J*u
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
avatar
x*o
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

avatar
J*u
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.

avatar
x*o
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.

avatar
J*u
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.

avatar
x*o
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.

相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。