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);
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:
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.