Second statement in begin block is not executed

Question

I have written this stored procedure in Oracle:

CREATE OR REPLACE FUNCTION GET_SOLVER_ID(username_in IN VARCHAR2) 
RETURN NUMBER 
IS 
solver_id number(19);
system_user_id number(19);
BEGIN 
SELECT id
INTO solver_id
FROM usr_solver
WHERE username = username_in;

select ID into system_user_id from USR_USER where USER_TYPE = 'X';
solver_id := nvl(solver_id, system_user_id);
RETURN(solver_id); 
END;

When I call the function with username that doesn't exist in table usr_solver I get null for the result. I expect to get system_user_id instead.
It seems like the other select statement and nvl function in begin block didn't execute.
Could you help, I can't see the reason why...

Thanks,
mismas

Your first select will have raised the NO_DATA_FOUND exception if the username doesn't exist, and the second select will not be run. You need to trap that exception and handle it appropriately. — Tony Andrews
I'm surprised that you are not getting ORA-01403: no data found exception. — Nitish
@Nitish they won't if calling the function in a select statement. — Tony Andrews
Tony Andrews is exactly right: the first SELECT ... INTO ... results in an error if the SELECT statement returns no rows. And when the function is called in a SELECT statement, the error is not raised up to the SQL statement (Oracle must have had their reasons for this odd behavior). Better than raising an error in that case would be to handle that possibility in the code itself. — mathguy

Sudipta Mondal Sudipta Mondal · Accepted Answer · 2017-06-19T14:19:49

This should do what you want

CREATE OR REPLACE FUNCTION GET_SOLVER_ID(
        username_in IN VARCHAR2)
    RETURN NUMBER
IS
    some_id NUMBER(19);
BEGIN
    BEGIN
        SELECT id
        INTO some_id
        FROM usr_solver
        WHERE username = username_in;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        SELECT ID
        INTO some_id
        FROM USR_USER
        WHERE USER_TYPE = 'X';
    END;
    RETURN(some_id);
END;

Second statement in begin block is not executed

1 Answers