Re: Intermittent ORA-01722 "invalid number"

From: Eric <>
Date: Thu, 14 Mar 2013 11:05:29 +0000
Message-ID: <>

On 2013-03-14, vsevolod afanassiev <> wrote:
> We started getting intermittent ORA-01722 "invalid number" in Oracle
> 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

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


ms fnd in a lbry
Received on Thu Mar 14 2013 - 12:05:29 CET

Original text of this message