Thursday, October 25, 2018

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

Hi,

Today I found a new thing in PLSQL. So far i thought when an INTO clause fails due to "ORA-01422: exact fetch returns more than requested number of rows" exception, it wont assign value to the variable.

But its not true. 

Even it throws the exception , the first value is captured in the variable.

Below is a small example for it. 
I tried to query the table with a condition which returns more than one row. 

CREATE TABLE dummy ( vname VARCHAR2 (20), vmark NUMBER);

INSERT INTO dummy VALUES ('Bala', 90);
INSERT INTO dummy VALUES ('Kaarhtik', 100);
INSERT INTO dummy VALUES ('Achu', 100);
INSERT INTO dummy VALUES ('Mithu', 100);

SELECT * FROM dummy;

SET SERVEROUTPUT ON

DECLARE
  v_name   VARCHAR2 (20);
BEGIN
  SELECT vname
    INTO v_name
    FROM dummy
   WHERE vmark = 100
   ORDER BY vname
   ;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Exception occurred : ' || SQLERRM);
    DBMS_OUTPUT.put_line ('Value of v_name is : ' || v_name);
END;
/


Script Output

Exception occurred  : ORA-01422: exact fetch returns more than requested number of rows
Value of v_name is : Achu