PL/SQL Validate/Insert/Update Status - retval vs. exception

Question

I'm always looking to improve and applying best practices. I read quite a bit about refactoring in the last weeks. I have to work with a lot of awful code and I produced some not so nice stuff too but I'm trying to change that. Thats no problem for most languages but I'm pretty new to PL/SQL so I just copied the style of the already written code.

After reading some tutorials I realized that a lot of our code is pretty much more C style code using retval instead exceptions etc.

We have a lot of functions like open cursor, loop through it, validate the data, trim it or make some string manipulation and insert it into another table, update the status etc. I wonder what a best practice solution would look like on something like this. Atm most functions look like this:

LOOP
  FETCH C_ABC INTO R_ABC;
  EXIT WHEN C_ABC%NOTFOUND OR C_ABC%NOTFOUND IS NULL;
  SAVEPOINT SAVE_LOOP;

  retval := plausibilty_check(r_ABC);

  IF (retval = STATUS_OK) THEN
    retval := convert_ABC_TO_XYZ(r_ABC, r_XYZ);
  END IF;

  IF (retval = STATUS_OK) THEN
    retval := insert_XYZ(r_XYZ);
  END IF;

  retval := update_ABC(r_ABC.PK_Id, retval);

END LOOP;

If i was using exceptions I guess I had to raise them inside the functions so I can handle them in the main function, if not everyone would have to crawl to every sub function to understand the program and where the updates happen etc. So I guess I would have to use another PL/SQL block inside the loop? Like:

LOOP
  FETCH C_ABC INTO R_ABC;
  EXIT WHEN C_ABC%NOTFOUND OR C_ABC%NOTFOUND IS NULL;
  SAVEPOINT SAVE_LOOP;

  BEGIN
    plausibilty_check(r_ABC);
    convert_ABC_TO_XYZ(r_ABC, r_XYZ);
    insert_XYZ(r_XYZ);
    update_ABC(r_ABC.PK_Id, STATUS_OK);
  EXCEPTION
    WHEN ERROR_CODE_XYZ THEN
      update_ABC(r_ABC.PK_Id, ERROR_CODE_XYZ);
  END
END LOOP;

I guess that function handles a pretty common problem but I still havn't found any tutorial covering something like this. Maybe someone more experienced with PL/SQL might gimme a hint what a best practice on a task like that would look like.

Answer 1

I like to use exceptions to jump out of a block of code if an exceptional event (e.g. an error) occurs.

The problem with the "retval" method of detecting error conditions is that it introduces a second layer of semantics on what a function is and for.

In principle, a function should be used to perform a calculation and return a result; in this sense, a function doesn't do anything, i.e. it makes no changes to any state - it merely returns a value.

If it cannot for some reason calculate that value, that would be an exceptional circumstance, so I'd want the function to raise an exception so that the calling program will not blindly continue on its merry way, thinking it got a valid value from the function.

On the other hand, a procedure is a method by which action is done - something is changed, something is validated, or something is sent. The normal expected path is that the procedure is executed, it does its thing, then it finishes. If an error occurs, I want it to raise an exception so that the calling program will not blindly continue thinking that the procedure has successfully done its thing.

Thus, the original intent of the fundamental difference between "procedures" and "functions" is preserved.

In languages like C, there are no procedures - everything is a function in a sense (even functions that return "void") - but in addition, there is no real concept of an "exception" - so these semantics don't apply. It's for this reason that the C style of returning an error/success flag don't translate well into languages like PL/SQL.

In your example, I'd consider doing it something like this:

BEGIN
  LOOP
    FETCH c_ABC INTO r_ABC;
    EXIT WHEN c_ABC%NOTFOUND;

    IF record_is_plausible(r_ABC) THEN

      r_XYZ := convert_ABC_TO_XYZ(r_ABC);

      insert_or_update_XYZ(r_XYZ);

    ELSE

      update_as_implausible(r_ABC);

    END IF;

  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    -- log the error or something, then:
    RAISE;
END;

So where the semantics of the operation is doing some validation or something, and returning a result, I converted plausibilty_check into a function record_is_plausible that returns a boolean.