Home » SQL & PL/SQL » SQL & PL/SQL » MAX() resulting in random -2112 errors (Oracle 10g, RHEL 8)
MAX() resulting in random -2112 errors [message #429431] Tue, 03 November 2009 15:04 Go to next message
jhughe90
Messages: 3
Registered: November 2009
Junior Member
Pro*C Query:

EXEC SQL SELECT TO_CHAR(MAX(B.TIMESTAMP),:o_ts_format)
INTO :o_max_action_entry_ts:o_max_action_entry_ts_ind
FROM SCHEMA.TABLE1 A,
SCHEMA.TABLE2 B
WHERE A.ID = :o_input_id
AND A.OTHER_FIELD = my_fixed_value
AND A.ID = B.ID;

We're getting occasional random -2112 (Too many rows) from this query. Our process then switches to a backup mode that validates off a flat file. We then requeue the transaction to be reprocessed every half hour so that a record can be stored for auditing purposes. When the job reruns later on, it again fails anywhere from 2-4 times with a 2112 before finally working with no data changes being made.

Without the MAX, some IDs do return multiple rows. With the MAX I see no reason why it would return multiple rows. I cannot duplicate the results using PL/SQL Developer query windows either. The data in these tables is fairly static and does not change often. Most of the B.TIMESTAMP values are years old.

The only pattern I see is that for the data conditions that are failing, B.TIMESTAMP is the same value when multiple rows match before the MAX call. MAX should still eliminate the possibility of a 2112 though. Is there a bug/patch out there that we need?
Re: MAX() resulting in random -2112 errors [message #429434 is a reply to message #429431] Tue, 03 November 2009 15:35 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is there, by any chance, a GROUP BY clause you didn't mention?
Re: MAX() resulting in random -2112 errors [message #429435 is a reply to message #429434] Tue, 03 November 2009 15:52 Go to previous messageGo to next message
jhughe90
Messages: 3
Registered: November 2009
Junior Member
No that is the full query.
Re: MAX() resulting in random -2112 errors [message #429436 is a reply to message #429435] Tue, 03 November 2009 16:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Can you please confirm that it is really an ORA-02112 error, and not a PCC-02122 Error, since you didn't actually mention that in the post, and a PCC-02122 would be something completely different?
Re: MAX() resulting in random -2112 errors [message #429439 is a reply to message #429436] Tue, 03 November 2009 16:36 Go to previous message
jhughe90
Messages: 3
Registered: November 2009
Junior Member
Definitely is ORA 2112.

10/29/2009-11:49:10.345[16750]auth: oracle error -2112 checking merchant >20898< status
10/29/2009-11:49:10.679[16750]auth: SQL-02112: SELECT..INTO returns too many row

I'm looking into the following query to see if that may be the cause instead. The logging error text that is printed out is the same in both of the sqlcode checks, so it could be either of these queries since there is no logging in between.


EXEC SQL SELECT DECODE(A.ACTION,'A',:o_active,'C',:o_active,:o_inactive)
INTO :o_status
FROM SCHEMA.TABLE2 A
WHERE A.ID = :o_input_id
AND A.TIMESTAMP = TO_DATE(:o_max_action_entry_ts,:o_ts_format);

It appears more likely that this is the real cause and needs to be fixed to return 1 row before decoding.



[Updated on: Tue, 03 November 2009 16:40]

Report message to a moderator

Previous Topic: Recompiling database objects
Next Topic: ORA-12704: Character set mismatch
Goto Forum:
  


Current Time: Mon Dec 05 08:47:15 CST 2016

Total time taken to generate the page: 0.11020 seconds