function called from sql fails silently when exception in function occurs
eisdevFeb 20 2009 — edited Feb 20 2009When I run an SQL statement with a function that raises an exception, shouldn't the query fail with an error? I thought this is what used to happen. WHat I am finding is that if on a given row the function fails, that row is silently discarded from the result set. If there is a bug or an intentional exception in the code, this is deceptive. What I've given here below is a simplified coding. Is there anyway to switch this behaviour? If not, I would be very scared to use anything but trivial functions in views from now on. We are using Oracle 10g. The behaviour is witnessed in both Toad and SQL Plus.
Here is the code:
My real life application is much more complex but this is what it distills down to.
-- running this gives no exceptions. It fails silently, by simply skipping that row and
-- the user is potentially unknowingly missing data in the event of an exception in the code.
-- if we select rows from a table in conjuction with this function,
-- we would not see the rows where that function failed. (in this case,
-- since the function always fails we would get nothing).
-- I would have expected that this would raise an exception in the host environment.
SELECT test_func
FROM dual;
-- On the other hand, running it as an anonymous block,
-- we see that clearly, the code is failing
DECLARE x NUMBER;
BEGIN
x:=test_func;
END;
-- the function itself, for simplicity make the code always raise
FUNCTION test_func RETURN NUMBER IS
BEGIN
RAISE no_data_found;
RETURN 1;
END;
Thanks
Jim
/