Forum OpenACS Q&A: Re: referencing a specific db pool

Collapse
Posted by Tom Jackson on

I know it works that way in plsql, and plpgsql is very similar, you add an exception block to the bottom of the function, something like:

   EXCEPTION
     WHEN OTHERS
     THEN
        return 1;
   END;

The WHEN OTHERS clause handles all exceptions not otherwise handled. If you exect certain types of errors and want to distinguish between them, you need to include them first.

If you have several dml statements that need to be executed, and you want as many of them to succeed as possible, you can follow the advice of _Oracle PL/SQL Programming, page 266-267 and have each statement in its own block:

 BEGIN
  BEGIN
   DELETE FROM employee WHERE ...;
  EXCEPTION
   WHEN OTHERS THEN NULL;
  END;

  BEGIN 
   UPDATE company SET ...;
  EXCEPTION
   WHEN OTHERS THEN NULL;
  END;
 END;

Using this method, you could do away with the second function, and use a sub-block to do the work:


 LOOP
   BEGIN
     ...;
   EXCEPTION
    WHEN OTHERS THEN NULL;
   END;
 END LOOP;

This works in pl/sql, you might try a simple example in pl/pgsql.