Function resumes execution after exception in subprogram

Question

I am raising an exception in a subprogram, and I'm expecting to see the calling function halt execution at this point. However, the calling function continues processing as if nothing happened, and I don't understand why.

My function looks something like this:

FUNCTION getFooCursor (i_blah IN VARCHAR)
    RETURN t_ref_cursor
IS
    v_sum_variable  NUMBER;
BEGIN
    --lookup number
    v_sum_variable := getNumber (i_blah);

    --call function that raises NO_DATA_FOUND exception
    doRaiseException();

    --the exception handler is only supposed to catch for this block
    BEGIN
        --do stuff and end up with a cursor
        RETURN barCursor(v_sum_variable);
    EXCEPTION
        WHEN OTHERS THEN
            --set some variables
    END
END;

Let's say doRaiseException() looks like this:

PROCEDURE doRaiseException ()
IS
BEGIN
    RAISE NO_DATA_FOUND;
END;

When I debug this function in TOAD, it helpfully informs me that the NO_DATA_FOUND exception has been raised. However, it then immediately carries on to execute the next line (where barCursor() is called) and the function finishes as if nothing ever went wrong.

I have tried replacing doRaiseException(); directly with RAISE NO_DATA_FOUND; for testing purposes (it actually does more than that) and this stops execution within getFooCursor() but whatever SQL calls it again completely ignores the exception.

Is this just how exceptions work in PL/SQL? Don't they bubble up as they do in Java or C#? Perhaps I am missing something crucial about exceptions in Oracle. How do I get an exception to bubble up to the host?

Here is my Oracle version (as returned from v$version):

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production                          
CORE    10.2.0.5.0  Production                                      
TNS for HPUX: Version 10.2.0.5.0 - Production                   
NLSRTL Version 10.2.0.5.0 - Production

Are you sure your first function looks exactly like that? Exceptions work as you suppose and "bubble up", so you must be catching it somewhere. — Ben, May 15 '13 at 15:10
@Ben: I've added some information to my example that suddenly seemed a lot more relevant after reading your comment. Obvious in retrospect... — Stephan B, May 15 '13 at 15:23

score 2 · Accepted Answer · answered May 15 '13 at 18:53

Your understanding of exceptions is correct. However, this is one notable exception to how exceptions work: NO_DATA_FOUND is silently ignored in a SQL context. This is a "feature", because this is how Oracle tells other processes that there is no more data to read.

For custom exceptions, you will probably need to catch NO_DATA_FOUND and raise it as different exception. This is usually a horrible way to handle exceptions but there is no good alternative here.

SQL> create or replace function function1 return number is
  2  begin
  3     raise no_data_found;
  4     return 1;
  5  end;
  6  /

Function created.

SQL> select function1 from dual;

 FUNCTION1
----------


1 row selected.

SQL> create or replace function function2 return number is
  2  begin
  3     raise no_data_found;
  4     return 1;
  5     exception when no_data_found then
  6             raise_application_error(-20000, 'NO_DATA_FOUND raised');
  7  end;
  8  /

Function created.

SQL> select function2 from dual;
select function2 from dual
       *
ERROR at line 1:
ORA-20000: NO_DATA_FOUND raised
ORA-06512: at "JHELLER.FUNCTION2", line 6

score 1 · Answer 2 · edited May 23 '17 at 12:11

Exceptions work as you suppose and "bubble up", so you must be catching it somewhere.

That's what's happening... you're catching every exception, which isn't the best practice. You can either ensure you only catch a specific exception if you define one yourself. However, that doesn't seem to be what you want to do here. You want to re-raise only a single exception.

So, you could define a custom exception in a separate package, raise that in your sub-program and then do something like this in your calling block:

begin
   RaiseException;

exception
   when my_exception_package.my_exception then
      raise;
   when others then
      DoSomethingElse;
end;

That way you catch the exceptions you want to raise and then re-raise them. If the exception is different then you continue with your current programme flow.

Function resumes execution after exception in subprogram

2 Answers2