Oracle Merge Rejection Row Count

Given an Oracle Merge statement with a rejection limit, is there a shorthand way to identify how many rows were rejected, without being forced to query the rejection destination?

The rejection destination has records pre-merge, so would currently have to be counted twice and the difference taken, but 开发者_Python百科it seems like there should be a counter exposed, given that it has to be aware of whether it has exceeded the rejection limit.

There is no exposed rejection counter.

The main purpose of DML Error Logging is to support ETL processes. It allows us to load huge amounts of data without a couple of errors forcing the entire load to rollback. I guess the underlying rationale is that errors will be resolved manually before we issue more DML against that table: it's not really intended to provide a permanent error log.

However, if you give each DML statement a unique tag you can count the exact number of rejections it has spawned.

declare
    l_tag varchar2(46);
    reject_cnt pls_integer;
begin

    ....

    l_tag := 'merging from source_table::'||to_char(sysdate, 'yyyymmddhh24miss');

    merge into target_table a
        using source_table b
        on (a.id = b.id)
      ....
      log errors into err$_target_table (l_tag) reject limit 10;

    select count(*)
    into reject_cnt
    from   err$_target_table
    where  ora_err_tag$ = l_tag;

    ....