Explicit Cursors For Better Program Control

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.