When querying values from a PL/SQL function, the output does not return the Oracle error that it should. The identical SQL returns an error in SQL Plus.
Note that the function should return 1 row but actually returns more than one row.
Example:
First, create function GETEMPNO
Function:
CREATE OR REPLACE FUNCTION Getempno(SAL_1 IN NUMBER)
RETURN NUMBER
IS
EMPNO NUMBER;
BEGIN
SELECT empno INTO empno FROM EMP WHERE sal = SAL_1;
RETURN EMPNO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0.0;
END;
/
Toad's output:
select GETEMPNO(3000) from dual;
0 rows selected
SQL*Plus's Output
select GETEMPNO(3000) from dual; ---- generates the error ----
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center