ORA-01422: exact fetch returns more than requested number of rows

This error normaly means that the query fetched more than one records , while it was expected to return only one record (in cases where you are using in clause in a query to assign the result to a variable , you are assuming it will always retun only one row but at the time of this exception it is returning more rows).

If this is the case the solution is very simple , introduce more strict criteria in your query to make sure that it alwasy returns only one row.

How ever I also faced this exception in another interesting case.

Problem:

In my oracle function I was passing a parameter and the name of the parameter was "alert_id" , then I was comparing the value of this parameter with a field in the query below


select a.message into html from  My_Table a where a.alert_id=alert_id;

I double checked there was only one record in the databas with that alert_id , but inside trhe function when i was running this query it was giving me ORA-01422 again which did not make sense.

Solution:

I found that the problem was that the name of the field in the database and the name of the parameter was same (alert_id in my case) , when you do that you will face this problem because oracle will pace the current value in the database field after the = sign , so my query was getting interprated as (suppose alertid in the data base is 5380)

select a.message  from  rtoc.rtoc_alert a where 5380=5380;

when I ran the above query in PL/SQL it showed me more than one records.

Moral of the story :)

Naver use the parameter names ina  function which have any possibility of exsitance in tables you are using in your function.

Suggessted Naming Convention

function parameters should start with "p_" ==> "p_alert_id"
 variables declared whould start with  "v_"