CODEWITHSUNDEEP
sqloracle-database
Omar
Omar

Reputation: 27

ORACLE Error report ORA-01422: exact fetch returns more than requested number of rows?

I am trying to run the below code but it give me error. I am new in ORACLE and any help are welcome, also what additions can be made to the code to ensure this error is captured in the future and a meaningful message is provided for the user thanks.

DECLARE
      V_lname  VARCHAR2(15);
BEGIN
      SELECT last_name INTO v_lname
       FROM employees
       WHERE first_name='John';
       DBMS_OUTPUT.PUT_LINE  ('John' 's last name is : ' 
|| v_lname);

END;

and I am having this error;

Error report ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4

Upvotes: 0

Views: 6061

Answers (2)

Littlefoot
Littlefoot

Reputation: 143163

Two useless options. Why useless? Because you should probably rewrite query so that it returns only desired "John".

Also, a suggestion: let variable reference column's datatype, don't fix it to e.g. varchar2(15). What if you - some day in the future - have to alter the table and enlarge the column to varchar2(25)? Your code will miserably fail.

The first option: use aggregate function, such as MIN or MAX:

DECLARE
  V_lname  employees.last_name%type;            --> this
BEGIN
  SELECT MAX(last_name)                         --> this
    INTO v_lname
    FROM employees
    WHERE first_name = 'John';

  DBMS_OUTPUT.PUT_LINE  ('John' 's last name is : ' || v_lname);
END;

Another option is to use ROWNUM:

DECLARE
  V_lname  employees.last_name%type;            --> this
BEGIN
  SELECT last_name
    INTO v_lname
    FROM employees
    WHERE first_name = 'John'
      AND rownum = 1;                           --> this

  DBMS_OUTPUT.PUT_LINE  ('John' 's last name is : ' || v_lname);
END;

Upvotes: 1

Andrew Sayer
Andrew Sayer

Reputation: 2336

The error message is clear, you can only fetch one value into your v_lname variable so PL/SQL is erroring to say that your query is trying to return multiple rows.

Either:

  • Change the query so it only returns one row
  • Change the PL/SQL so that it can handle multiple rows.

I'll demo the second option, as I have no idea which employee named 'John' you might want.

begin
  for rJohn in 
    (select last_name
     from   employees
     where  first_name='John'
    ) loop
      dbms_output.put_line  ('John''s last name could be : '|| rJohn.last_name);
   end loop;
end;
/

Here I use an implicit cursor for loop (https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/cursor-FOR-LOOP-statement.html#GUID-62C9A3C8-82F9-468F-8D84-81672E67736D) to run a dbms_output.put_line call per row it finds for your query.

Upvotes: 1

Related Questions