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: Mark C. Stock <mcstockX_at_Xenquery>
Date: Thu, 15 Apr 2004 13:14:07 -0400
Message-ID: <obidnURmJ79pX-PdRVn-gg@comcast.com>

"Joel Garry" <joel-garry_at_home.com> wrote in message news:91884734.0404121531.4f4a5669_at_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

keep in mind that in 8.1.7, the SQL parser in the RDBMS engine is different than the SQL parser imbedded in the PL/SQL engine -- so if the SQL is indeed exactly the same, the parser in the RDBMS engine may be a handling explicit data type conversion differently

;-{ mcs Received on Thu Apr 15 2004 - 12:14:07 CDT

Original text of this message

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