Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Long shot, but here goes

Re: Long shot, but here goes

From: Joel Garry <joel-garry_at_home.com>
Date: 12 Apr 2004 16:31:20 -0700
Message-ID: <91884734.0404121531.4f4a5669@posting.google.com>


D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com> wrote in message news:<407A406B.2010301_at_orindasoft.com>...
> "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.

I had a similar situation with a package generated by a portal tool. Turned out there was a need for an alias on the column in the select for the tool to generate the proper column type to receive the number.  The column was subtracting dates, IIRC, worked fine in sqlplus.

>
> 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

jg

--
@home.com is bogus.
American Trash, indeed. 
http://www.signonsandiego.com/uniontrib/20040410/news_h1b10enron.html
Received on Mon Apr 12 2004 - 18:31:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US