FETCH INTO doesn't raise an exception if the set is empty, does it?

Posted by Cade Roux on Stack Overflow See other posts from Stack Overflow or by Cade Roux
Published on 2011-02-02T23:15:09Z Indexed on 2011/02/02 23:25 UTC
Read the original article Hit count: 177

Filed under:
|
|
|

Here is some actual code I'm trying to debug:

BEGIN
            OPEN bservice (coservice.prod_id);

            FETCH bservice
            INTO  v_billing_alias_id, v_billing_service_uom_id, v_summary_remarks;

            CLOSE bservice;

            v_service_found := 1;
        -- An empty fetch is expected for some services.
        EXCEPTION
            WHEN OTHERS THEN
                v_service_found := 0;
        END;

When the parametrized cursor bservice(prod_id) is empty, it fetches NULL into the three variables and does not throw an exception.

So whoever wrote this code expecting it to throw an exception was wrong, right? The comment seems to imply that and empty fetch is expected and then it sets a flag for later handling, but I think this code cannot possibly have been tested with empty sets either.

Obviously, it should use bservice%NOTFOUND or bservice%FOUND or similar.

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about exception