Error logging with MERGE query in Oracle 11

Question

I am using Oracle 11 and I have millions of records in my table. I am using a MERGE statement to update records from source table to target table.

At any moment while updating these millions of records, how can I log errors per record?

For example: I have successfully updated 400 records, but while updating 401st record, I am getting some error, so in this case how can I log something like

401st record and its failure cause

So that from these millions of records I can identify for which records the query has failed.

Answer 1

You don't get to pick your own error, but you can use the error_logging_clause. See Oracle documenation at https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606.

You will first need to create an error log table with DBMS_ERRLOG. At the end of your merge statement you need to tack on

LOG ERRORS INTO <<errorLogTableName>> 

Answer 2

You can log the errors in the same table or a table of your choosing. In the example provided below I created a table called tst_merge and populated it with 7 rows of data into 2 columns column1 and data_to_update to keep it simple. To maintain this simplicity I created the table with a 3rd column to store any Oracle Errors but this column can be in ANY table - preferably in an table that is used to track these errors.

This answer also assumes that you are at least somewhat comfortable with PL/SQL. I am unable to think of a way for this to be possible without PL/SQL using my example.

SETUP the table and insert Data Code is below:

CREATE TABLE "TST_MERGE"
  (
    "COLUMN1" NUMBER,
    "DATA_TO_UPDATE" CHAR(9 BYTE),
    "DB_ERROR" VARCHAR2(200)
  )
;

INSERT INTO tst_merge (column1, data_to_update) VALUES (1001, 'dataInRow');
INSERT INTO tst_merge (column1, data_to_update) VALUES (7001, 'dataInRow');
INSERT INTO tst_merge (column1, data_to_update) VALUES (3001, 'dataInRow');
INSERT INTO tst_merge (column1, data_to_update) VALUES (4001, 'dataInRow');
INSERT INTO tst_merge (column1, data_to_update) VALUES (5001, 'dataInRow');
INSERT INTO tst_merge (column1, data_to_update) VALUES (6001, 'dataInRow');
INSERT INTO tst_merge (column1, data_to_update) VALUES (2001, 'dataInRow');

The CODE to do what you are requesting is below. PL/SQL.

DECLARE
  /* Store Error Code and Message so we can log these values into a table */ 
  sql_error_num   NUMBER          :=  0;
  sql_error_msg   VARCHAR2(100)   :=  '';

  CURSOR cur_MergeData IS
    SELECT column1, data_to_update
      /* The below line builds a character n times as the new string to update into the data_to_update field */
      /* Here this is simply going to force the error ORA-12899 'Value Too Large for Column'                 */
      , rpad('L', (to_number(SUBSTR(column1, 1, 1)) * 2), 'L') AS new_string
    FROM tst_merge
    ;

  TYPE t_MergeData IS TABLE OF cur_MergeData%ROWTYPE; /* Type declared based on Cursor     */
  c_MergeData  t_MergeData  :=  t_MergeData();        /* Collection declared based on Type */


BEGIN

  OPEN cur_MergeData;
    FETCH cur_MergeData BULK COLLECT INTO c_MergeData; /* Fill Collection with data from Cursor */
  CLOSE cur_MergeData;

  IF c_MergeData.COUNT > 0 THEN
    FOR r IN c_MergeData.FIRST .. c_MergeData.LAST
    LOOP

      BEGIN
          /* Output row data just for troubleshooting */
          dbms_output.put_line(c_MergeData(r).column1 ||' '|| c_MergeData(r).new_string );

          /* Merge Code */
          MERGE INTO tst_merge tm USING
          (
          SELECT column1, data_to_update
          FROM /* When Merging into the same table the MERGE INSERT ONLY works if a record is returned. */
               /* The dual table forces a record with columns values of NULL to be returned if NO MATCH is found. */
            (SELECT 1 AS fake FROM dual) d
            LEFT JOIN tst_merge t ON t.column1 = c_MergeData(r).column1
          ) m ON (m.column1 = tm.column1)

          WHEN MATCHED THEN       

          UPDATE SET data_to_update = c_MergeData(r).new_string
          WHERE column1 = c_MergeData(r).column1

          WHEN NOT MATCHED THEN

          INSERT (column1, data_to_update)
          VALUES (c_MergeData(r).column1, c_MergeData(r).new_string)
          ;

      EXCEPTION       
        WHEN OTHERS THEN 
          sql_error_num :=  SQLCODE;
          sql_error_msg :=  SQLERRM;

          DBMS_OUTPUT.put_line('Error '||TO_CHAR(sql_error_num)||': '||sql_error_msg);

          UPDATE tst_merge SET db_error = ('Error '||TO_CHAR(sql_error_num)||': '||sql_error_msg)
          WHERE column1 = c_MergeData(r).column1;

      END;          

    END LOOP;
  END IF;

END
;

Gif below to see it in action