How to log all exceptions in Oracle package?

Never use SQLERRM or SQLCODE. Exception logging without storing the line number is cruel.

Always use dbms_utility.format_error_stack||dbms_utility.format_error_backtrace, or something similar.

For example, the following block shows the error but not where it happened:

declare
    v_number number;
begin
    v_number := 1/0;
exception when others then
    dbms_output.put_line(sqlerrm);
end;
/

DBMS Output:
ORA-01476: divisor is equal to zero

This block shows both the error and where it happened. This is critical information for troubleshooting any non-trivial program.

declare
    v_number number;
begin
    v_number := 1/0;
exception when others then
    dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
/

DBMS Output:
ORA-01476: divisor is equal to zero
ORA-06512: at line 4

---

(Below is some generic exception handling advice.)

For real code you would not want to simply output the error information. In fact, the best exception handling strategy is usually to do nothing, and use Oracle's default behavior to display all the error messages and line numbers.

Custom exception handling in Oracle is generally only useful in one of these three cases:

1. You are going to do something with a specific error, like call another procedure of just ignore the error.
2. If this is a database-only program, then you might want to catch and log exceptions at the top of the program, at the (limited number) of entry points. You don't need exception handling if the application already catches everything, or for inner code. Exceptions propagate up the stack, it's best to catch them at the end.
3. If you need to log some specific value that only exists as a local variable, it has to be logged immediately, since that value won't propagate with the exception.

There's a lot of PL/SQL code that blindly catches and logs errors in every procedure. That is unnecessary and usually counter-productive.

You can't use SQLERRM directly - you have to assign it to an intermediate variable. Note that Oracle 9i would let you get away with it, but that has always been the documented behavior. See here for some sample code.

You could also consider wrapping this bit in an autonomous transaction, so it gets logged even if your PL/SQL code's transaction gets rolled back.