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
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