在Oracle 11中使用合并查询记录错误

Question

我正在使用Oracle 11，我的表中有数以百万计的记录。我使用MERGE语句从源表更新记录到目标表。

在任何时候，当更新这数百万条记录时，我如何记录每条记录的错误？

例如:我已经成功地更新了400条记录，但是在更新401条记录时，我得到了一些错误，所以在本例中，我如何记录类似的内容

401记录及其失效原因

因此，我可以从这数百万条记录中识别出查询失败的记录。

Answer 1

您不能选择自己的错误，但可以使用error_logging_clause。请参见9016.htm#SQLRF01606的Oracle文档化。

您首先需要使用DBMS_ERRLOG创建一个错误日志表。在合并语句的末尾，您需要修改

LOG ERRORS INTO <<errorLogTableName>> 

Answer 2

您可以将错误记录在相同的表或您选择的表中。在下面提供的示例中，我创建了一个名为tst_merge的表，并将7行数据填充到2列column1和data_to_update中，以保持简单。为了保持这种简单性，我创建了带有第三列的表，以存储任何Oracle错误，但该列可以位于任何表中--最好是在用于跟踪这些错误的表中。

这个答案还假设您至少对PL/SQL比较满意。用我的例子，我想不出一种没有PL/SQL就能做到这一点的方法。

安装表并插入数据代码如下：

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');

下面是执行所请求的代码。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可以看到它的作用

​

​