Oracle logging message to a table.

create table log_test (message varchar2(255));
create table log_results (resultvalue number);


CREATE OR REPLACE PROCEDURE LOG_MESSAGE (MESSAGE_IN IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO LOG_TEST (MESSAGE) VALUES (MESSAGE_IN);
COMMIT;
END;
/

create or replace
PROCEDURE CREATE_ERROR (VALUE_IN IN NUMBER) IS
l_x NUMBER;
BEGIN
LOG_MESSAGE( 'Attempting to divide by ' || to_char(VALUE_IN) );
l_x := 10 / VALUE_IN;
INSERT INTO log_results (resultvalue) values (l_x);
LOG_MESSAGE( 'Finished to divide by ' || to_char(VALUE_IN) );
COMMIT;
END;
/

select * from log_test;

SQL> select * from log_test;

no rows selected

SQL> select * from log_results;

no rows selected

SQL> exec create_error(2);

PL/SQL procedure successfully completed.

SQL> exec create_error(1);

PL/SQL procedure successfully completed.

SQL> exec create_error(0);
BEGIN create_error(0); END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TPM.CREATE_ERROR", line 5
ORA-06512: at line 1


SQL> exec create_error(4);

PL/SQL procedure successfully completed.

SQL> select * from log_results;

RESULTVALUE
-----------
5
10
2.5

SQL> select * from log_test;

MESSAGE
--------------------------------------------------------------------------------
Attempting to divide by 2
Finished to divide by 2
Attempting to divide by 1
Finished to divide by 1
Attempting to divide by 0
Attempting to divide by 4
Finished to divide by 4

7 rows selected.