Intermittent ORA-01722 "invalid number"

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Wed, 13 Mar 2013 17:25:10 -0700 (PDT)
Message-ID: <4e65e710-d138-4085-8adb-bd6057a04c49_at_googlegroups.com>

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

PROCEDURE PROCESS_ORDERS IS (OrderNo IN NUMBER, ...) ORDER_TYPE NUMBER(2);
BEGIN
SELECT NVL(MAX(ORDER_TYPE),0) INTO ORDER_TYPE <-- This line generates error FROM ORDERS WHERE ORDER_NO = OrderNo;

Column ORDER_TYPE has type NUMBER(2).

Obviously declaring local variable with the same name as table column isn't very good programming practice and we will be changing this stored procedure. While this syntax is confusing it is still valid from Oracle perspective, the stored procedure is in VALID state and it works most of the time.

In this database we have trigger on servererror, this trigger records all occurrences of all errors and stores them in a table. The table shows that we had 20 - 30 occurrences of ORA-01722 per day until last week when out of the blue it went to 4,000 - 5,000 per day. The number went down on weekend (as expected) and it was low on Monday, but on Tuesday and Wednesday we started getting them again, although number per 24 hours was around 1,000.

We tried to enable event 1722 (alter system set events = '1722 trace name errorstack level 3'). I tested it by running SELECT TO_NUMBER('123ABC') FROM DUAL; It generated ORA-01722 and trace file was produces, it contained SQL statement I was executing in 'name=SELECT..' line. However no trace files were generated when this error occurred in the application, so there is nothing to send to Oracle to analyze.

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. Received on Thu Mar 14 2013 - 01:25:10 CET