Re: Long shot, but here goes

"invalid number" in the middle of a query could be because your 'where' clause tries to compare a number column with a character column. It may do this by casting the character column to number. If your table has a single row where the character column can't be turned into a number you end up in a situation where "invalid numnber" comes up every time the exucution plan involves looking at that row. In my experience PL/SQL is pickier about this stuff than Sql*Plus.

David Rolfe
Orinda Software
Dublin, Ireland

www.orindasoft.com

Remove 'FRUITBAT' for a valid email address

> I'm running Oracle 8.1.7 for Solaris.
>
> I have this long complicated query that returns 416 rows. When I try
> and embed the query in a package's stored procedure,
>
> BEGIN
> DECLARE
> CURSOR abc IS long_complicated_query;
> BEGIN
> DELETE FROM SUMMARY_SPROG_UTIL;
> FOR abc_rec IN abc
> LOOP
>
> I get the error
>
> BEGIN
> *
> ERROR at line 1:
> ORA-01722: invalid number
> ORA-06512: at "SUPPORT.SUMMARY_USAGE_PKG", line 183
> ORA-06512: at "SUPPORT.SUMMARY_USAGE_PKG", line 16
> ORA-06512: at line 6
>
>
> referring to this line, "FOR abc_rec IN abc" of all things. Oddly,
> when I run this same query through the command line, it runs, but when
> I try and put it in the package's stored procedure, I get this crazy
> error. Does anyone have any suggetsions as to how I should start
> debugging this problem?
>
> Thanks, - Dave
Received on Mon Apr 12 2004 - 02:08:27 CDT