ORA-01422 Error Occurred When I Call Procedure with Number Parameter

oracleplsqlplsql-developerprocedure-definitiontoad

I want to write PL/SQL Procedure that updates salary which is less than 2000. I wrote this procedure.And when i call it with integer id 'ORA-01422: exact fetch returns more than requested number of rows' error is thrown by TOAD. My procedure is like below:

DECLARE
PROCEDURE update_salary(ID customers.id%type) is
   c_sal  customers.salary%type;
BEGIN
  SELECT salary 
  INTO c_sal 
  FROM customers 
  WHERE id = ID;
   dbms_output.put_line ('Before update operation salary is:' || c_sal);

  --dbms_output.put_line ('Parameter :' || ID);

   IF (c_sal <= 2000) THEN
      UPDATE customers 
      SET salary =  salary + 1000
      WHERE id = ID;
      dbms_output.put_line ('Salary updated');
   END IF;

  SELECT salary 
  INTO c_sal 
  FROM customers 
  WHERE id=ID;
   dbms_output.put_line ('After update operation salary is:' || c_sal);
END;

BEGIN
update_salary(1);
END;
/

I print parameter id with dbms_output. The parameter is coming correctly. How can i fix this error !

Best Answer

Naming your parameter the same way you named your column is quite unfortunate.

  SELECT salary 
  INTO c_sal 
  FROM customers 
  WHERE id = ID;

In the above statement, typing id in lowercase or ID in uppercase makes no difference, it is case insensitive. Just because you named your PL/SQL parameter ID (in uppercase), does not mean the database will use it when you type ID (in uppercase) in the above. Even if the id in customers has unique values, the above SELECT returns all* rows from the table, because the id column precedes the ID parameter, and id always* equals ID (*except for NULLs).

You could just simply use a different name for your parameter, for example I usually use the p_ prefix for them, so your ID parameter could be changed to P_ID:

DECLARE
PROCEDURE update_salary(P_ID customers.id%type) is
   c_sal  customers.salary%type;
BEGIN
  SELECT salary 
  INTO c_sal 
  FROM customers 
  WHERE id = P_ID;
   dbms_output.put_line ('Before update operation salary is:' || c_sal);

  --dbms_output.put_line ('Parameter :' || ID);

   SELECT  salary 
   INTO  c_sal
   FROM customers
   WHERE id = P_ID;
   IF (c_sal <= 2000) THEN
      UPDATE customers 
      SET salary =  salary + 1000
      WHERE id = P_ID;
      dbms_output.put_line ('Salary updated');
   END IF;

  SELECT salary 
  INTO c_sal 
  FROM customers 
  WHERE id=P_ID;
   dbms_output.put_line ('After update operation salary is:' || c_sal);
END;

BEGIN
update_salary(1);
END;
/
Related Question