Re: ORA-01722: invalid number

[Nathan Treadway <nathant@ONTKO.COM>]

> When porting SOR reports from SQLBase to Oracle
> I am geting a lot of conversion problems some of
> wich I understand and other that I don't.
>
> For example I am geting:
> (SQR 5528) ORACLE OEXEC error -1722 in cursor 47:
>    ORA-01722: invalid number
>
> when executing:
> begin-select
> sum(antalhwfel)          &fsantalhwfel
> sum(drifttimmar)    &fsdrifttimmar
> from felstat
>      where     failurecode = $felkod and
>           ( ( ar = #year and
>           manad < #month ) or
>           (ar = #f-year and
>           manad > #month ) )
> end-select

What are the column definitions for your columns?  Are any of type CHAR or
VARCHAR (other than failurecode)?

I have seen this error occur when I was using a CHAR/VARCHAR column that
almost always had a valid number in it.  If you give oracle an expression
like "ar = 44" when ar is a VARCHAR column, it will try to covert each value
into a number and then do a numeric comparison.  This works fine until you
hit a row that doesn't contain a valid number.

The tricky part about this error is that it is highly dependant on what rows
are processed, and even possibily what columns, and so the query may work
sporatically depending on factors internal to Oracle's query processing.

You might try commenting out various lines of your select list and where
clause to see if you can isolate which column is causing your problem.
Also, try hard-coding the constants you used in your sql*plus testing into
an sqr program, just to make sure that the query is not being processed
differently.

If this is the problem, you can probably fix it by putting  "to_char(...)"
around the #variables, though you have to be careful about string v.s.
numeric comparison (i.e. '1000' < '2').  Or you can fix your data (though
that leaves you open to this problem in the future).

Hope this helps.

                                                        Nathan

----------------------------------------------------------------------------
Nathan Treadway    | Ray Ontko & Co. | Software consulting services
nathant@ontko.com  | Richmond, IN    | http://www.ontko.com/