The only errors you should trap are:
a) those errors that are NOT actually errors. for example -
...
begin
select x into y from t where ...
exception
when no_data_found then x := some_valid_default;
end;
....
Here, no_data_found is not an error (it COULD be, but not in this particular case - our logic dictated otherwise). We catch IT and only IT and deal with it. All other errors are in fact TRUE ERRORS and we cannot deal with them.
b) as I did above with a when others followed by a RAISE. The exception block should be as small as possible (and you can have as many as you need in block of code, you do NOT want or need that 'catch all' exception block at the end in general). They surround some resource that needs to be manually cleaned up in the event of an error.
Looking at my example above:
...
31 l_cursor := dbms_sql.open_cursor;
32 begin
33 dbms_sql.parse( l_cursor, l_array, 1, l_array.count, TRUE, dbms_sql.native );
34 l_status := dbms_sql.execute( l_cursor );
35 exception
36 when others
37 then
38 dbms_sql.close_cursor( l_cursor );
39 RAISE;
40 end;
41 dbms_sql.close_cursor( l_cursor );
...
See how the exception block is just there to protect the cursor handle. I only make sure the cursor gets closed and then RERAISE the error. You want to keep the block of code as small as possible, but as large as it needs to be.
But most importantly - you RERAISE the exception!!! the caller MUST KNOW that the procedure failed miserably. The cannot ignore the exception!!! (a return code on the other hand is easily overlooked - don't use return codes)
c) at the top most level of your code - at the original block of plsql that your client code invoked. Instead of just calling "flashback_schema", your application could:
begin
flashback_schema( x, y );
exception
when others
then
call_error_logging_routine_that_uses_an_autonomous_transaction;
RAISE;
end;
Your error logging routine would write diagnostic information into a logging table (using dbms_utility to get the error stack) and then re-raise the exception so that the client knows about it.
You can use raise_application_error instead - to return your own custom error message - but by all means - return an ERROR, not a return code, not a message - an honest to goodness ERROR.
and this is not just a best practice of PLSQL - but a best practice in all exception based languages like Java, C++, C# and so on.