| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Unexplainable SQL-error message
A copy of this was sent to no_F.Th.G.Heijmans_at_inter.nl.net_spam (F.Th.G.
Heijmans)
(if that email address didn't require changing)
On Sun, 07 Nov 1999 13:26:14 GMT, you wrote:
>Hi,
>
>Can anyone tell me where this error-message comes from
>
> 1 select to_number(accountnr)
> 2 from invoces
> 3 where projectnr = 'R1099'
> 4 and to_number(accountnr) > 0
>
>TO_NUMBER(ACCOUNTNR)
>---------------------
> 1040706
> 313102015
>
[snip]
>ERROR:
>ORA-01722: invalid number
>
>Accountnr is stored in the dabase as a varchar2(10). When I run the
>same query without the >0 there is no problem
>
> 1 select to_number(accountnr)
> 2 from invoces
> 3 where projectnr = 'R1099'
>
>This is not at all what I would expect. All the numbers are fine, I
>checked that. Appearantly ORA-01722 is not the real error.
>
it is the real error -- you really do have an accountnr that is not a number. you have a projectnr other then R1099 in the database which an ACCOUNTNR that is *not a number*. If accountnr is in fact a number, its datatype should be number. Consider this example based on yours:
tkyte_at_8.0> create table invoces(accountnr varchar2(10), projectnr varchar2(10)); Table created.
tkyte_at_8.0> insert into invoces values( '10101', 'R1099' ); tkyte_at_8.0> insert into invoces values( 'NotANum', 'NOT R1099' ); tkyte_at_8.0> insert into invoces values( '11111', 'R1099' ); tkyte_at_8.0> select to_number(accountnr) 2 from invoces 3 where projectnr = 'R1099' 4 and to_number(accountnr) > 0
no rows selected
That fails since the first row the query hit was 10101, R1099 (and that works) and then it tried to evaluate 'NotANum', 'NOT R1099'. since the predicate is evaluated in *some* order -- it is using the default order of 'bottom up' processing. We first to_number(accountnr) to see if its greater then zero -- ORA-1722 is raised. The reason I see NO ROWS (and you see 15 exactly - and always) is that SQLPlus array fetches by default 15 rows at a time. Since I get the error on the first fetch -- no rows printed (my row in error is row 2). In your case, the first fetch succeeds -- the first 15 rows read from the table are OK, all numbers. The second batch of 15 however have a row with an accountnr that is NOT a number.
That is why the query:
tkyte_at_8.0> select to_number(accountnr)
2 from invoces
3 where projectnr = 'R1099'
4 /
TO_NUMBER(ACCOUNTNR)
10101
11111
works always -- the to number on accountnr is not applied until AFTER the predicate is performed, so since all accountnr's in project R1099 are valid -- this works.
Likewise, knowing that the predicate is being done from the bottom up, i can query:
tkyte_at_8.0> select to_number(accountnr)
2 from invoces
3 where to_number(accountnr) > 0
4 and projectnr = 'R1099'
5 /
TO_NUMBER(ACCOUNTNR)
10101
11111
and that works since the project number is done first and we only to_number accountnr's in the project R1099.
You have some bad data in your table -- you should really store numbers in numbers and if you are storing it in a varchar because it sometimes is not a number you better not use to_number on it ever else you will get this error at sometime..
>If I vary the size of the comparison (>0, >1000000) I get a different
>list, but it still stops after the 15th row with the same error.
>
>Is there anyone who can explain this to me?
>
>Regards,
>
>Frank
>
>
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Nov 07 1999 - 08:51:32 CST
 |
 |