FETCH INTO doesn't raise an exception if the set is empty, does it?
- by Cade Roux
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.
