| MAX() resulting in random -2112 errors [message #429431] |
Tue, 03 November 2009 15:04  |
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 #429436 is a reply to message #429435] |
Tue, 03 November 2009 16:00  |
ThomasG
Messages: 3212
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 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
