ora-01422 error in SELECT INTO statements

Question

I have one row in my Company_Person_all view named by 'YENER UZUN' in EMPLOYEE_NAME column (I already want only one result). When I send parameter to this function (fname, instead of using 'YENER UZUN') I encounter

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

What should I do to prevent this error? Also when I write the code below ('YENER UZUN', instead of fname) it's ok it doesn't give me an error.

FUNCTION Get_Calistigi_Santiye_By_Fname(fname IN varchar2)
    RETURN varchar2 
IS
    temp_ varchar2(100);
BEGIN
    select free_field6
    into   temp_
    from   company_person_all
    where  employee_name = 'YENER UZUN';

    DBMS_OUTPUT.put_line(temp_);
    RETURN temp_;
END;

That shouldn't happen. How are you calling the function and passing in the `fname` value? — Tony Andrews, Aug 23 '17 at 11:05
BEGIN dbms_output.put_line('something': '||company_person_api.Get_Calistigi_Santiye_By_Fname('YENER UZUN')); END; /* like this */ — Uğur Sinan Sağıroğlu, Aug 23 '17 at 11:08
AND this code returns only one row to me ... select * from Company_Person where employee_name ='YENER UZUN'; — Uğur Sinan Sağıroğlu, Aug 23 '17 at 11:10
Debug it - change the procedure to loop though the returned rows and write out their employee_names and ROWIDs or something like that. — Tony Andrews, Aug 23 '17 at 11:30

Uğur Sinan Sağıroğlu · Answer 1 · 2017-08-24T05:43:54.787

0

I solved it by changing 'fname' parameter name to 'xyz'. 'fname' was being used a RECORD instance name by other procedures and functions in the package. So that when i changed parameter name the error instantly fixed.

edited Aug 24 '17 at 05:43

answered Aug 23 '17 at 14:00

Uğur Sinan Sağıroğlu

11
5

I, atleast dont know about any global paramter in Oracle called `fname` . – XING Aug 23 '17 at 14:23
this is an ERP system based on Oracle, named IFS. I did not understand why it solved my problem exactly. But changing parameter name is not a joke , it solved this error. – Uğur Sinan Sağıroğlu Aug 23 '17 at 15:12
Dont follow that you are not sure about. It might help you now but somewhere else it would hurt. try my solution and check if that works. – XING Aug 23 '17 at 18:18
Your function doesn't use `fname` so it is not clear how that name collision could be the issue. – William Robertson Aug 25 '17 at 22:48

score 0 · Answer 2 · answered Jul 23 '18 at 12:38

Mostly, Using a cursor instead of select .. into is a shortcut to avoid ORA-01422 with a proper order by(asc [default] / desc) clause due to which of the records prefer for your business logic(last or first record ) as in the following :

FUNCTION Get_Calistigi_Santiye_By_Fname( fname company_person_all.employee_name%type )
    RETURN company_person_all.free_field6%type 
IS
    temp_ company_person_all.free_field6%type;
BEGIN
  for c in
   (
    select free_field6 ff6
      from company_person_all
     where employee_name = fname --> 'YENER UZUN'
     order by work_date_time
   )
   loop
    temp_ :=  c.ff6;
   end loop;    
    dbms_output.put_line(temp_);

    RETURN temp_;
END;

ora-01422 error in SELECT INTO statements

2 Answers2