ORA-01427: single-row subquery returns more than one row
A developer friend of mine sent me an email today frustrated that none of the DBA’s he worked with could explain this error to him and sent it onto me. I thought I would get a post out with this one as I think it trips up a lot of folks:
“I have a report query that worked fine on Wednesday and today produced a ‘single-row query returns too many rows’ error. So when I went to track it back through Toad, the query just flat out stopped working. ”
Here was my explanation and a few hints to correct the problem:
It’s not your query, per say, but your data and you may have to change a query to take into consideration the change in data, even if it isn’t the one that you think you need to.
The error you are receiving is due to a subset of data that it requires that used to return only one row that now returns two, (which is what the error text is trying to tell us.) It requires one row to ensure the values that are being passed to it to fulfill the statements that rely on this data. Sometimes you can simply put a “distinct” into your select and fix the problem, but before you can decide that, what you are looking for is some pre-required query that is used to fulfill the failing statements variables, so let’s say, (just making up a coding scenario here…)
Procedure P_UPD_MEMBER is(p_mbr_cd);
and begins:
Cursor v_mbr_id is:
Select member_id from members where member_cd=p_mbr_cd;
This query would then be utilized to populate the member_id field for the next 1, 2, 3,etc. statements as it runs through the procedural code.
The problem is, the data has changed and due to a three column requirement on the primary key, they are allowed to have non-unique member_id’s where in the past they were unique. Let’s say our p_mbr_cd that we passed into our procedure is ‘CTGIS’. If we query the table and it returns the following, we’ve got a problem:
MEMBER_ID MEMBER_CD MEMBER_BLK
22129 CTGIS 1234
22129 CTGIS 9876
So now, your query for your cursor is returning two rows instead of one, invalidating your cursor’s requirements for one row, so when the next statement, let’s say it looks something like:
Update MEMBER_DIM
……
where member_id=v_mbr_id;
Guess what you get? ORA-01427: single-row subquery returns more than one row!! How can it place two values into the v_mbr_id of this statement?
To correct the issue, first diagnose what the problem is:
Look at the statement that populates each of your variables for the statement that is failing.
Run the select and see if more than one row is returned.
If more than one row does return, then inspect the data and see if there is an easy change to your select statement for the cursor that will ensure it returns one row and you will be back in business.
If not, then go back to the people that are loading the data and tell them to stop screwing it up! You will then need to go back, correct the data issue and rerun the procedure to have it complete successfully.
Kellyn
You can't get the ORA-1427 in the way you've shown. In your example you would get the TOO_MANY_ROWS exception raised by the select.
You *could* get the ORA-1427 if (with the same data) you combined the two statements:
UPDATE member_dim
WHERE member_id = (
SELECT member_id
FROM members
WHERE member_cd = p_mbr_cd
)
Or of course, if you just queried the data from the member_dim table with this subquery.
You can also get ORA-1427 if you have a subquery as one of your result columns, and it returns more than one row…
ORA-1427 often means your data model is screwy (and/or your developers don't understand it).
Regards Nigel
"[…]Kellyn Pedersen wears rugged DBA Kevlar. Here is her post, ORA-01427: single-row subquery returns more than one row.[…]"
Log Buffer #171
I thought people would be interested in the ultimate outcome – we completed the query weeks ago but the testers were tying out the data and numbers for 4 years of data so it just got completed. Anyway …
I was never really concerned with the 'single-row subquery …' error as I know what I need to do to trace that back and tweak accordingly. My real issue was the fact that the query was working fine performance-wise one day and then flat out would not return a result set OR the above error the next day (even when left running for 90+ minutes over an extended lunch-break).
Basically, the report we're building is really more of a data warehouse report than one that should be run in a transactional environment (but we don't have a data-warehouse …. yet). It's not impossible to do, it's simply much trickier. Add in all the 'ifs', 'ands', 'buts', 'and then/but if' scenarios for calculating the critical piece of information (Net Margin per Sales Order Line), it's even more complicated – especially as the end-users want it to be real-time leaving me with the challenge of writing the SQL query to handle everything.
Everything looked fine in explain plans – I was hitting the right indexes and stats were appropriate. There was a full table scan but it was something unavoidable (we were reporting on all sales order from BOT so oe_order_headers_all was getting scanned – it actually sped through this).
The real problem came when I was trying to retrieve the PO Unit Cost and the AR Invoice Unit Cost (or Final Unit Cost) for each item on the sales order. Bear in mind, that there will not always be a PO associated with the Order and there may not be an Invoice yet – the plot thickens 🙂 This was where the serious performance degradation came in (still not sure why it worked one day but not the next – oh well).
In addition, having it in the transactional system meant that the data is a constantly moving target; you could also have multiple shipments for one order line, multiple po lines for one order line etc.
Long story short, I rewrote the entire thing and got the query to return ALL data from BOT in 54 seconds (their current report which is based off a custom table and is only accurate once per day at 7:55 a.m., takes 20 minutes to run).
My only outstanding piece to complete was retrieving the PO Unit Cost for Internal Sales Order lines that have not yet been invoiced (as once it has been invoiced, the Invoice Unit Cost is the value used for the net margin calculation). This probably amounts to no more than 3-5% of scenarios. If I try to hit the PO tables real-time in the query it kills performance simply because of my limited options in terms of linking the data (For External lines I have oe_drop_ship_sources which has all linked information). I tried to use mtl_reservations but unfortunately, that table does not maintain data long-term. Once the reservation is completed, it gets deleted.
We already had a process that updates sales order lines with the PO Number in an attribute column. We decided to add to this – including the po line id so I don't even have to join to the po tables. We did, of course, have to take into account things like PO Line Cancellations or Updates but the end-user is actually fine with this approach. For these 3-5% of scenarios, it may not be really 'real-time' but they are ultimately concerned with the Invoiced Unit Price down-stream. The process runs every 30 minutes. Also, they have access to the process to run it themselves if they need to, prior to running the report. Having this value to hand meant a significant performance increase and accurate data.