CODEWITHSUNDEEP
oracle-databaseplsqlselect-intoora-01422
Uğur Sinan Sağıroğlu
Uğur Sinan Sağıroğlu

Reputation: 11

ora-01422 error in SELECT INTO statements

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;

Upvotes: -1

Views: 176

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

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;

Upvotes: 0

Uğur Sinan Sağıroğlu
Uğur Sinan Sağıroğlu

Reputation: 11

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.

Upvotes: 0

Related Questions