Re: Intermittent ORA-01722 "invalid number"

From: Eric <eric_at_deptj.eu>
Date: Thu, 14 Mar 2013 11:05:29 +0000
Message-ID: <slrnkk3bnp.3h0.eric_at_teckel.deptj.eu>

On 2013-03-14, vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com> wrote:
> We started getting intermittent ORA-01722 "invalid number" in Oracle
> 9.2.0.8 on AIX.
> According to the application logs the error comes from PL/SQL stored
> procedure, error message includes line number. However this stored
> procedure has been created in 2008 and hasn't been changed since then. It
> has only one IN parameter: OrderNo (order number). Users are telling us
> that the error is intermittent - they get it for certain order number
> and when they re-try later for the same order it works. I wasn't able
> to re-produce the error by running this stored procedure from a loop
> for all possible values of OrderNo.
> The SQL that generates error is

<snip>
> I searched metalink and found a reference to a bug in 9.2 that may cause
> ORA-01722. However this bug should affect only INSERT ... SELECT, not
> normal SELECT.

Well, the other question is what has changed -

* oracle patches applied
* changes in the way statistics are collected
* oracle parameter changes
* different uses of the relevant procedure
* increased use of the relevant procedure.

I have a vague memory (unable to chase up at the moment unfortunately) of a bug relating to cached query plans that could cause ORA-01722. The workaround was to force the offending query to be re-parsed, either by flushing the shared pool (overkill and possibly a bad idea when the system is busy), or by analyzing a table used by the query.

Eric

-- 
ms fnd in a lbry

Received on Thu Mar 14 2013 - 12:05:29 CET