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