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
