CODEWITHSUNDEEP
oracle-databaselogginginsert
OceanWavez
OceanWavez

Reputation: 303

oracle table insert transaction log

I have an application that is built on top of a framework; the framework handles all the communication and requests to/from the DB. I have Oracle 12c.

The application is an electronic document management system; I have one document when trying to commit it will generate an error, the error is not that self-explanatory as begin controlled by the framework, the error related to the DB commit process. I tried to check the constraint/Index for that specific table related to the eDoc with no luck.

How I can get more log from oracle itself when inserting the data, hoping I can get a better idea of why the error is happening.

Regards

Upvotes: 0

Views: 1526

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

If you have a test database that you can reproduce the error in, you can create an AFTER SERVERERROR trigger to log any errors that occur.

If you try this in production, you risk large-scale problems for all users and applications accessing the database, especially if you make a mistake. Doing this in a dev/QA instance is strongly recommended!

Here is a short example of how to do that:

-- Create a table to hold the error log
CREATE TABLE matt1_errors (
    error_ts        TIMESTAMP,
    stacktrace      VARCHAR2(4000),
    sql_stmt        VARCHAR2(4000)); 
/

-- Create a trigger to log any server errors that occur
CREATE TRIGGER matt_log_errors AFTER SERVERERROR ON DATABASE
DECLARE
  l_sql_pieces_list DBMS_STANDARD.ora_name_list_t;
  piece_count PLS_INTEGER;
  l_sql_text VARCHAR2(4000);
BEGIN
  piece_count := ora_sql_txt(l_sql_pieces_list);

  FOR i IN 1..piece_count LOOP
    l_sql_text := substr(l_sql_text || l_sql_pieces_list(i), 1, 4000);
  END LOOP;

  INSERT INTO matt1_errors ( error_ts, stacktrace, sql_stmt)
     VALUES (SYSTIMESTAMP, dbms_utility.format_error_stack, l_sql_text);

EXCEPTION 
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE (dbms_utility.format_error_stack);
END;

Test it...

-- Create a table to insert into.
CREATE TABLE matt1 ( id NUMBER, text VARCHAR2(4) );
-- Cause an error.
insert into matt1 (id, text) VALUES (1, 'Too long!!!');

-- See what we got...
select * from matt1_errors;


+---------------------------------+--------------------------------------------------------------------------------------+--------------------------------------------------------+
|            ERROR_TS             |                                      STACKTRACE                                      |                        SQL_STMT                        |
+---------------------------------+--------------------------------------------------------------------------------------+--------------------------------------------------------+
| 06-NOV-19 03.02.18.083116000 PM | ORA-12899: value too large for column "APPS"."MATT1"."TEXT" (actual: 11, maximum: 4) | insert into matt1 (id, text) VALUES (1, 'Too long!!!') |
+---------------------------------+--------------------------------------------------------------------------------------+--------------------------------------------------------+

So,

  1. Create the AFTER SERVERERRORS trigger
  2. Reproduce your error
  3. Check the table that your trigger writes to

Upvotes: 1

Related Questions