CODEWITHSUNDEEP
oracle-databasefunctionstored-proceduresexceptionplsql
Pop Stack
Pop Stack

Reputation: 944

In PL/SQL (Oracle), can exceptions propagate across function/procedure calls?

as it happens in enclosed blocks
If an anonymous block calls a function that raises exceptions, those are neither shown on console nor trapped in enclosing blocks...
What's more, after being caught by handler in the function, lines after function call in the anonymous block are executed normally!

The called procedure is: 

CREATE OR REPLACE PROCEDURE qt(pno number, qty OUT number)
IS
BEGIN
select sum(qty_on_hand) into qty from products where productno=pno;
END;

The calling block is: 

DECLARE
qty number;
BEGIN
qt(&pno, qty);
dbms_output.put_line('qty is: '||qty);
END;

In case of invalid product number, no error is shown; why?

Upvotes: 1

Views: 5162

Answers (3)

Dave Costa
Dave Costa

Reputation: 48111

In your specific example, I think that no exception is being raised at all. You say "in the case of an invalid product number", by which I assume you mean a product number that does not exist. That sounds like you expect your query to throw NO_DATA_FOUND, but since it is using an aggregate function without a GROUP BY, it will actually return a single row containing NULL if there are no matching rows.

Upvotes: 6

Rene
Rene

Reputation: 10541

Just to add to Tony's answer. You may not know what type of exception a calling function may throw. In this case you can do:

EXCEPTION
  WHEN exception_that_may_occur_in_my_function THEN
    ...
    RAISE_APPLICATION_ERROR(-20001,'My error message');

  WHEN others THEN  -- Any exception that can come from a function I'm calling
    RAISE;

END;

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

If the exception is caught by the handler in the function and not re-raised then no exception will be triggered in the caller. This is correct behaviour. If you want the exception to be visible to the caller you must re-raise it in the function using the RAISE command:

FUNCTION fun ...
...
EXCEPTION
  WHEN some_exception THEN
    ...
    RAISE;
END;

Alternatively you can raise a different exception e.g.

EXCEPTION
  WHEN some_exception THEN
    ...
    RAISE_APPLICATION_ERROR(-20001,'My error message');
END;

Upvotes: 8

Related Questions