Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Intermittent ORA-01722 "invalid number"
424 views
Skip to first unread message
vsevolod afanassiev
Mar 13, 2013, 8:25:10 PM3/13/13
to
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.
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.
Eric
Mar 14, 2013, 7:05:29 AM3/14/13
to
On 2013-03-14, vsevolod afanassiev <vsevolod....@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>
> 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
> 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 -
> ORA-01722. However this bug should affect only INSERT ... SELECT, not
> normal SELECT.
* 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
Mladen Gogala
Mar 14, 2013, 7:45:25 AM3/14/13
to
On Wed, 13 Mar 2013 17:25:10 -0700, vsevolod afanassiev wrote:
> 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.
The trace wasn't generated because "ALTER SYSTEM" affects only behavior of
> 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.
the newly started processes. Restart the application and the trace will
appear.
--
http://mgogala.byethost5.com
Mark D Powell
Mar 14, 2013, 1:21:49 PM3/14/13
to
On Wednesday, March 13, 2013 8:25:10 PM UTC-4, vsevolod afanassiev 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 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.
vsevolod, I would modify the code immediately to make sure the issue is not related to pl/sql scoping rules. Prefix the pl/sql variable witl v_ or l_ and label the table as in "orders o" then prefix the table columns in the select.
> 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.
HTH -- Mark D Powell --
vsevolod afanassiev
Mar 14, 2013, 4:57:20 PM3/14/13
to
Thanks, I've checked V$SESSION and indeed all connections have been created before we enabled tracing.
0 new messages