Arrgh! I just spent several days working through a bug in a large (1000+ line) unfamiliar package that was raising unexpected NO DATA FOUND errors.
After working through several calls to the procedure and monitoring the results I isolated the problem to code very similar to the following.
CREATE OR REPLACE PROCEDURE abc AS v_a VARCHAR2(1); PROCEDURE def IS v_b VARCHAR2(1); BEGIN SELECT NULL INTO v_b FROM another_table WHERE 1 = 2; END; BEGIN -- get row from a_table BEGIN SELECT NULL INTO v_a FROM a_table WHERE 1 = 2; EXCEPTION WHEN NO_DATA_FOUND THEN -- if row not found then run def procedure def; END; END; /
A simple piece of code right? Nope. The call to the DEF procedure in the EXCEPTION handler was raising the NO DATA FOUND error which was not handled.
This caused the calling code to fail.
This brings up one of the things I don’t like to use in PL/SQL – implicit cursors. I agree they are simpler to type than explicit ones but when their associated EXCEPTION handlers are used for program flow it can quickly get unruly. I much prefer explicit cursors such as in this example:
CREATE OR REPLACE PROCEDURE abc AS v_a VARCHAR2(1); CURSOR curs_a IS SELECT NULL FROM a_table WHERE 1 = 2; CURSOR c_another IS SELECT NULL FROM another_table WHERE 1 = 2; PROCEDURE def IS v_b VARCHAR2(1); BEGIN OPEN c_another; FETCH c_another INTO v_b; CLOSE c_another; END; BEGIN -- self documenting!? -- easy to follow! OPEN curs_a; FETCH curs_a INTO v_a; IF curs_a%NOTFOUND THEN def; END IF; CLOSE curs_a; END;
Alright that’s enough ranting for today.
Thanks for reading.