Oracle PL/SQL Best Practices

Name

EXC-02: Use the default exception-handling model to communicate module status back to calling PL/SQL programs.

Synopsis

Watch out for carrying baggage from other languages into the world of PL/SQL. Your last language might not have had a sophisticated error-handling architecture. As a consequence, you relied on parameters in every program to pass back error status (code and message).

Don’t do this in PL/SQL! Rely on the default model: raise exceptions and handle those exceptions in the separate exception section of your blocks.

Example

Here’s the kind of code you want to avoid:

BEGIN
   overdue.analyze_status (
      title_in,
      start_date_in,
      error_code,
      error_msg);

   IF error_code != 0
   THEN
      err.log (...);
      GOTO end_of_program;
   END IF;

   overdue.send_report (
      error_code,
      error_msg);

   IF error_code != 0
   THEN
      err.log (...);
      GOTO end_of_program;
   END IF;

Benefits

Your executable sections are clean, simple, and easy to follow. You don’t have to check for status after every program call. You simply include an exception section to trap and deal with crises as they arise.

Challenges

It can be hard to break old habits.

You might inherit code that looks like the example. In this case, I would suggest to your manager that it’s worth it to proactively clean up the code.

If you are calling PL/SQL from a non-Oracle frontend, you may need to pass back error information (see [EXC-03: Catch all exceptions and convert to meaningful return codes before returning to non-PL/SQL host programs.]).