How to create error log for stored procedure in oracle 10g?

Question

I need an example of creating error log file for stored procedure in oracle. please give me an example with table creation and stored procedure creation and error log creation.

Thanks in advance

EDIT (relevant info from other question)

Suppose there is a stored procedure. When I am executing that stored procedure, some expected error/exception may occur, so I need to create an error log table in which all the errors will automatically be store whenever I will execute the stored procedure.

For example, if there is some column which does not allow null values, but the user is entering null values, then that error should be generated and it should stored in the error log table.

Solution 1

You haven't really given a lot of detail about your requirements. Here is a simple error log table and a procedure to log error messages into it:

CREATE TABLE error_log (ts TIMESTAMP NOT NULL, msg VARCHAR2(4000));

CREATE PROCEDURE log_error (msg IN VARCHAR2) IS
BEGIN
  INSERT INTO error_log (ts, msg)
  VALUES (SYSTIMESTAMP, SUBSTR(insert_log.msg, 1, 4000));
END log_error;

You might or might not need it to be an autonomous transaction. That would depend on whether you want the log to record errors from procedures that rollback their changes.

Typically, this will be implemented in a more generic logging system which would log not only errors, but warnings and debug info too.

If you want a DML statement (insert/update/delete) to log an error for each row (instead of just failing on the first row that errors), you can use the LOG ERRORS clause - instead of the statement failing, the statement will succeed, and the rows that were not inserted/updated/deleted will be written to the error log table you specify, along with the error code and error message applicable. Refer to the link provided by vettipayyan.

If you want all exceptions that are raised within a procedure to be logged, you can catch them with WHEN OTHERS:

BEGIN
  -- your code here
EXCEPTION
  WHEN OTHERS THEN
    log_error(DBMS_UTILITY.format_error_stack);
    log_error(DBMS_UTILITY.format_error_backtrace);
    RAISE;
END;

Solution 2

Here's the page with code samles:
DML ErrorLogging

Comments

[DCookie]

+1, essentially the same thing I've done time and time again. Definitely look at autonomous transactions - much of the time you want the log entry ESPECIALLY when you hit an error that rolls back everything!