Debugging in SQLPLUS, need help - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

Debugging in SQLPLUS, need help

Debugging in SQLPLUS, need help# Database - 数据库

J*u2099-12-16 08:12

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*o2099-12-16 08:12

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*u2099-12-16 08:12

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*o2099-12-16 08:12

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*u2099-12-16 08:12

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*o2099-12-16 08:12

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.