INSERT with LOG ERRORS and ERROR ROWCOUNT

View All Scripts Login to Run Script

Script Name INSERT with LOG ERRORS and ERROR ROWCOUNT
Description The script provides a solution for counting number of ERROR records when performing INSERT ... SELECT ... LOG ERRORS ... statements. It also shows how to count errors when using multi table insert with error logging. PLEASE NOTE that the rollbacks performed in this script were executed along with the insert for the rollback to take effect. Each statement in a script, and each 'Run' from within the SQL Worksheet, will have an implicit commit at the end due to the stateless nature of Live SQL. If this script is executed as-is within Live SQL, your results will differ because the rollback is a separate statement.
Area SQL General / Data Manipulation
Contributor Jacek Gebal
Created Sunday February 09, 2020

Statement 1

Create the demo table to insert data into

Create test table

create table target_table (id integer primary key , value_1 integer, value_2 integer)

Table created.

Statement 2
Create an error log table for the TARGET_TABLE. Bu default the created error table is named as the base table with "ERR$_" prefix. So in this case an ERR$_TARGET_TABLE error table will be created.
Create error log table for test table
```
call dbms_errlog.create_error_log('target_table')
```
```
Statement processed.
```
Statement 3
Only one row gets inserted into TARGET_TABLE. Note the "1 row(s) inserted." feedback message. If we wouldn't know how many rows are in source data (in this case two), we can't figure out how many rows went into error table.
Try to insert two identical rows into the table
```
insert into target_table(id, value_1, value_2)  
select 1,2,3  
  from dual  
  connect by level <=2  
  log errors reject limit unlimited
```
```
1 row(s) inserted.
```
Statement 4
Note that rollback statement has no impact on error table - see below statements
```
rollback
```
```
Statement processed.
```

Statement 5

Data is not persisted after rollback

select count(1) from target_table


COUNT(1)
0

Statement 6
Data persists in ERR$_TARGET_TABLE despite ROLLBACK command executed in the session. Each failed row gets inserted into error table and each inset is committed as an autonomous transaction.
Data persists in error table despite rollback
```
select count(1) from err$_target_table
```
```
COUNT(1)
1
```

COUNT(1)
0

COUNT(1)
1

Statement 7

In this approach, count of the source data is done before inserting it to see how many rows are to be inserted. After the insert is performed, SQL%ROWCOUNT is used to check how many of source rows got inserted. Downsides: - source data needs to be scanned twice - source query is duplicated - potential performance bottleneck on large source data set or slow source data query

Approach 1 - count number of error rows by looking at source rows

declare  
  l_source_count integer;  
  l_insert_count integer;  
begin  
  select count(1)   
    into l_source_count   
    from ( 
    select 1,2,3  
      from dual  
      connect by level <=2  
  );  
    
  insert into target_table(id, value_1, value_2) 
    select 1,2,3  
      from dual  
      connect by level <=2  
    log errors reject limit unlimited;  
  l_insert_count := SQL%ROWCOUNT;  
  
  dbms_output.put_line('Inserted '||l_insert_count||' out of '||l_source_count||' source rows');  
  dbms_output.put_line((l_source_count - l_insert_count)||' rows rejected and placed in error table');  
end;

Statement processed.
Inserted 1 out of 2 source rows
1 rows rejected and placed in error table

Statement 8
```
rollback
```
```
Statement processed.
```
Statement 9
Note that error count keeps increasing. Remember - error data is always committed autonomously when using LOG ERRORS
```
select count(1) from err$_target_table
```
```
COUNT(1)
2
```

COUNT(1)
2

Statement 10

Note the C_TAG constant. It is used to identify errors generated by the particular statement. It's good idea to make this tag unique for statement execution. In this case, TIMESTAMP is used to make it unique. Next the C_TAG is used to count records inserted into error table. Downsides: - performance of scan on ERROR table could be poor when a lot of data exists in it - this could be mitigated by adding an index though you still need to perform a query on data that just got populated few lines above. This is not ideal and there could be a better approach to avoid scanning source data twice or performing a lookup on data that just got populated.

Approach 2 - count errors created by specific statement

declare  
  l_error_count  integer;  
  l_insert_count integer;  
  c_tag          constant varchar2(2000) := to_char(current_timestamp, 'yyyy-mm-dd hh24:mi:ssxff')||' insert';  
begin  
  insert into target_table(id, value_1, value_2) 
    select 1,2,3  
      from dual  
      connect by level <=2  
    log errors ( c_tag ) 
    reject limit unlimited;  
  
  l_insert_count := SQL%ROWCOUNT;  
  
  select count(1)  
    into l_error_count   
    from err$_target_table 
    where ora_err_tag$ = c_tag;  
  
  dbms_output.put_line('Inserted '||l_insert_count||' out of '||(l_error_count+l_insert_count)||' source rows');  
  dbms_output.put_line(l_error_count||' rows rejected and placed in error table');  
end;

Statement processed.
Inserted 1 out of 2 source rows
1 rows rejected and placed in error table

Statement 11
```
rollback
```
```
Statement processed.
```

Statement 12

This helper package aids in counting rows. Examples below will illustrate it's usage.

Create helper package to aid counting rows

create or replace package dml_utils as  
  
  subtype object_name is varchar2(258);  
  subtype row_count is naturaln;  
  no_object constant object_name := '[{no table}]';  
    
  -- add row to a counter for table name   
  procedure count_row( p_table_name object_name );  
  -- add row to a counter for table name and return 1  
  function count_row( p_table_name object_name := no_object ) return integer;    
  
  -- add row to a counter for table name and return the p_txt given as input parameter  
  function count_row( p_table_name object_name, p_txt varchar2 ) return varchar2;    
  
  -- return a counter for table name   
  -- set the counter for reset on next call to count_row  
  function get_count( p_table_name object_name := no_object) return row_count;  
    
  -- returns a SQL to create a trigger for counting rows on a specified table  
  function  generate_count_trigger( p_table_name object_name ) return varchar2;  
  
  -- creates a trigger for counting rows on a specified table  
  procedure create_count_trigger( p_table_name object_name );  
  
  -- drops trigger for counting rows on a specified table  
  procedure drop_count_trigger( p_table_name object_name );  
end;

Package created.

Statement 13

create or replace package body dml_utils as 
 
  type row_count_info is record ( 
    cnt         row_count := 1, 
    needs_reset boolean := false 
  ); 
  type table_row_counts is table of row_count_info index by object_name; 
   
  g_first_count row_count_info; 
 
  g_row_counts_for_table table_row_counts; 
 
  procedure count_row( p_table_name object_name ) is 
  begin 
    if g_row_counts_for_table.exists( p_table_name )  
      and not g_row_counts_for_table( p_table_name ).needs_reset then 
      g_row_counts_for_table( p_table_name ).cnt := g_row_counts_for_table( p_table_name ).cnt + 1; 
    else 
      g_row_counts_for_table( p_table_name ) := g_first_count; 
    end if; 
  end; 
 
  function count_row( p_table_name object_name := no_object ) return integer is   
  begin 
    count_row( p_table_name ); 
    return 1; 
  end; 
 
  function count_row( p_table_name object_name, p_txt varchar2 ) return varchar2 is 
  begin 
    count_row( p_table_name ); 
    return p_txt; 
  end; 
   
  function get_count( p_table_name object_name := no_object ) return row_count is 
    l_result row_count := 0; 
  begin 
    if g_row_counts_for_table.exists( p_table_name ) then 
      g_row_counts_for_table( p_table_name ).needs_reset := true; 
      l_result  := g_row_counts_for_table( p_table_name ).cnt; 
    end if; 
    return l_result; 
  end; 
 
  function generate_count_trigger( p_table_name object_name ) return varchar2 is 
    l_table_name   object_name := dbms_assert.sql_object_name( p_table_name ); 
    l_trigger_name object_name := l_table_name||'_AI'; 
  begin 
    return ' 
     create or replace trigger '||l_trigger_name 
     ||' before insert on '||l_table_name||q'[ for each row 
     declare 
     begin 
       dml_utils.count_row(']'||upper(l_table_name)||q'['); 
     end;]'; 
  end; 
 
  procedure create_count_trigger( p_table_name object_name ) is 
    pragma autonomous_transaction; 
  begin 
    execute immediate generate_count_trigger(p_table_name); 
  end; 
 
  procedure drop_count_trigger( p_table_name object_name ) is 
    pragma autonomous_transaction; 
  begin 
    execute immediate 'drop trigger '||dbms_assert.sql_object_name( p_table_name )||'_AI'; 
  end; 
   
end;

Package Body created.

Statement 14

In this example, note the WHERE condition We call the DML_UTILS.COUNT_ROW() function. This function will increase row count each time it's called. Benefits: - no need to scan source data twice to get count of inserted/source/error data Downsides: - function calls in SQL could cause performance degradation, when data-set is large - function is called for all rows, so the overhead exists even if no errors occur

Approach 3 - use counter package to count source rows

declare  
  l_source_count integer;  
  l_insert_count integer;  
begin  
  insert into target_table(id, value_1, value_2) 
    select 1,2,3  
      from dual  
      where dml_utils.count_row() > 0  
      connect by level <=2  
  log errors reject limit unlimited;  
      
  l_insert_count := SQL%ROWCOUNT;  
  l_source_count := dml_utils.get_count();  
  
  dbms_output.put_line('Inserted '||l_insert_count||' out of '||(l_source_count)||' source rows');  
  dbms_output.put_line((l_source_count-l_insert_count)||' rows rejected and placed in error table');  
end;

Statement processed.
Inserted 1 out of 2 source rows
1 rows rejected and placed in error table

Statement 15
```
rollback
```
```
Statement processed.
```
Statement 16
Note that TARGET_TABLE_2 does not have a primary key.
Create another target table
```
create table target_table_2 (id integer, value_1 integer, value_2 integer)
```
```
Table created.
```

Statement 17

Create error table for TARGET_TABLE_2

call dbms_errlog.create_error_log('target_table_2')

Statement processed.

Statement 18

In this example the DML_UTILS.COUNT_ROWS() function is used in TWO places to provide information about data inserts into specific tables. SQL%ROWCOUNT only provides total count of rows inserted. Note that the calls to DML_UTILS.COUNT_ROWS() will only count ATTEMPTED INSERTS, not the successful ones. Drawbacks: - With this approach we cannot really figure out the number of records inserted into specific error table - This approach will execute the DML_UTILS.COUNT_ROWS() for each processed rows, even if no rows get inserted into error table. This can have negative impact on performance of the SQL statement when processing large volumes of data.

Approach 4 - Partial solution - using counters for multi-table inserts

declare  
  l_insert_count integer;  
  l_error_count integer;  
begin  
  insert all  
    when dml_utils.count_row('T1') > 0 then  
      into target_table(id, value_1, value_2) 
      log errors reject limit unlimited  
    when dml_utils.count_row('T2') > 0 then  
      into target_table_2(id, value_1, value_2) 
      log errors reject limit unlimited  
    select 1,2,3  
      from dual  
      connect by level <=2  
  ;  
      
  l_insert_count := SQL%ROWCOUNT;  
  l_error_count  := dml_utils.get_count('T1') + dml_utils.get_count('T2') - l_insert_count;  
  
  dbms_output.put_line('Inserted '||l_insert_count||' rows');  
  dbms_output.put_line('Attempted to insert '||dml_utils.get_count('T1')||' rows into TARGET_TABLE');  
  dbms_output.put_line('Attempted to insert '||dml_utils.get_count('T2')||' rows into TARGET_TABLE_2');  
  dbms_output.put_line(l_error_count||' errors found while inserting into TARGET_TABLE or TARGET_TABLE_2');  
end;

Statement processed.
Inserted 3 rows
Attempted to insert 2 rows into TARGET_TABLE
Attempted to insert 2 rows into TARGET_TABLE_2
1 errors found while inserting into TARGET_TABLE or TARGET_TABLE_2

Statement 19
```
rollback
```
```
Statement processed.
```
Statement 20
This statements creates a trigger on error table. The trigger will get invoked ONLY when data gets inserted into specified error table. Trigger will call the DML_UTILS.COUNT_ROW for each row inserted into specified error table
Create trigger on error table
```
call dml_utils.create_count_trigger('ERR$_TARGET_TABLE')
```
```
Statement processed.
```

Statement 21

Create trigger on second error table

call dml_utils.create_count_trigger('ERR$_TARGET_TABLE_2')

Statement processed.

Statement 22

When calling DML_UTILS.GET_COUNT, reference the error table name to get the count that was calculated by trigger calls. The call to DML_UTILS.GET_COUNT( 'ERR$_TARGET_TABLE' ) returns number of rows inserted into ERR$_TARGET_TABLE

Approach 5 - count rows on error data using triggers

begin  
  insert all  
    into target_table(id, value_1, value_2)  
      log errors  
      reject limit unlimited  
    into target_table_2(id, value_1, value_2)  
      log errors  
      reject limit unlimited  
    select 1,2,3  
      from dual  
      connect by level <=2  
  ;  
  dbms_output.put_line('Inserted '|| SQL%ROWCOUNT||' rows');  
  dbms_output.put_line(dml_utils.get_count('ERR$_TARGET_TABLE')||' errors found while inserting into TARGET_TABLE');  
  dbms_output.put_line(dml_utils.get_count('ERR$_TARGET_TABLE_2')||' errors found while inserting into TARGET_TABLE_2');  
end;

Statement processed.
Inserted 3 rows
1 errors found while inserting into TARGET_TABLE
0 errors found while inserting into TARGET_TABLE_2

Statement 23
```
rollback
```
```
Statement processed.
```

Statement 24

This approach gives you most thorough counters. Keep in mind that function calls on large volume data will bring overhead. It can really impact your performance. If you only want to know how much data was inserted into error tables without querying them - Approach 5 seems most suited.

Approach 6 - count both attempted inserts and rows on error data using triggers

declare  
  l_insert_count    integer;  
  l_t1_insert_count integer;  
  l_t2_insert_count integer;  
begin  
  insert all  
    when dml_utils.count_row('T1') > 0 then  
      into target_table(id, value_1, value_2)  
        log errors 
        reject limit unlimited  
    when dml_utils.count_row('T2') > 0 then  
      into target_table_2(id, value_1, value_2)  
        log errors 
        reject limit unlimited  
    select 1,2,3  
      from dual  
      connect by level <=2  
  ;  
      
  l_insert_count    := SQL%ROWCOUNT;  
  l_t1_insert_count := dml_utils.get_count('T1') - dml_utils.get_count('ERR$_TARGET_TABLE');  
  l_t2_insert_count := dml_utils.get_count('T2') - dml_utils.get_count('ERR$_TARGET_TABLE_2');  
  
  dbms_output.put_line('Inserted '||l_insert_count||' rows');  
  dbms_output.put_line('Inserted '||l_t1_insert_count||' rows into TARGET_TABLE');  
  dbms_output.put_line('Inserted '||l_t2_insert_count||' rows into TARGET_TABLE_2');  
  dbms_output.put_line(dml_utils.get_count('ERR$_TARGET_TABLE')||' errors found while inserting into TARGET_TABLE');  
  dbms_output.put_line(dml_utils.get_count('ERR$_TARGET_TABLE_2')||' errors found while inserting into TARGET_TABLE_2');  
end;

Statement processed.
Inserted 3 rows
Inserted 1 rows into TARGET_TABLE
Inserted 2 rows into TARGET_TABLE_2
1 errors found while inserting into TARGET_TABLE
0 errors found while inserting into TARGET_TABLE_2

Statement 25

For details on fixed issues see my blog post here: http://www.oraclethoughts.com/sql/insert-log-errors-and-sqlrowcount-continued/

Improved version of helper package

create or replace package dml_utils as

  subtype object_name is varchar2(258);
  subtype row_count is naturaln;
  no_object constant object_name := '[{no table}]';

  /**
    Adds row to a counter for specified item
  */
  procedure count_row( p_name object_name );

  /**
    Adds row to a counter for specified item and returns 1
  */
  function count_row( p_name object_name := no_object ) return integer;

  /**
    Adds row to a counter for specified item and returns the provided p_txt
  */
  function count_row( p_name object_name, p_txt varchar2 ) return varchar2;

  /**
    Returns a value of counter specified item without resetting counter
  */
  function peek_count( p_name object_name := no_object) return row_count;

  /**
    Returns a value of counter specified item and resets counter
  */
  function pop_count( p_name object_name := no_object) return row_count;

  /**
    Resets counter for specified item
  */
  procedure reset_count( p_name object_name := no_object);

  /**
    Return statement to create a trigger for counting rows on a specified table
  */
  function  generate_count_trigger( p_name object_name ) return varchar2;

  /**
    Creates a trigger for counting rows on a specified table
  */
  procedure create_count_trigger( p_name object_name );

  /**
    Drops trigger for counting rows on a specified table
  */
  procedure drop_count_trigger( p_name object_name );
end;

Statement 26

The key improvement is replacement of `get_count` function with `pop_count` and `peek_count` functions. Also additional procedure `reset_count` was added for completeness.

Improved version of helper package (body)

create or replace package body dml_utils as

  type table_row_counts is table of row_count index by object_name;

  g_items_counter table_row_counts;


  function get_counter_value(p_name object_name, p_reset boolean) return row_count is
    l_result row_count := 0;
  begin
    if g_items_counter.exists( p_name ) then
      l_result := g_items_counter( p_name );
    end if;
    if p_reset then
      reset_count( p_name );
    end if;
    return l_result;
  end;

  -- implementations for public declarations
  procedure count_row( p_name object_name ) is
  begin
    if g_items_counter.exists( p_name ) then
      g_items_counter( p_name ) := g_items_counter( p_name ) + 1;
    else
      g_items_counter( p_name ) := 1;
    end if;
  end;

  function count_row( p_name object_name := no_object ) return integer is
  begin
    count_row( p_name );
    return 1;
  end;

  function count_row( p_name object_name, p_txt varchar2 ) return varchar2 is
  begin
    count_row( p_name );
    return p_txt;
  end;

  function peek_count( p_name object_name := no_object ) return row_count is
  begin
    return get_counter_value( p_name, false );
  end;

  function pop_count( p_name object_name := no_object ) return row_count is
  begin
    return get_counter_value( p_name, true );
  end;

  procedure reset_count( p_name object_name := no_object) is
  begin
    if g_items_counter.exists( p_name ) then
      g_items_counter.delete( p_name );
    end if;
  end;

  function generate_count_trigger( p_name object_name ) return varchar2 is
    l_table_name   object_name := dbms_assert.sql_object_name( p_name );
    l_trigger_name object_name := l_table_name||'_AI';
  begin
    return '
     create or replace trigger '||l_trigger_name
      ||' before insert on '||l_table_name||q'[ for each row
     declare
     begin
       dml_utils.count_row(']'||l_table_name||q'[');
     end;]';
  end;

  procedure create_count_trigger( p_name object_name ) is
    pragma autonomous_transaction;
  begin
    execute immediate generate_count_trigger(p_name);
  end;

  procedure drop_count_trigger( p_name object_name ) is
    pragma autonomous_transaction;
  begin
    execute immediate 'drop trigger '||dbms_assert.sql_object_name( p_name )||'_AI';
  end;

end;

Statement 27

Usage of `pop_count` functions for retrieval of error table counts

New helper package in action

begin
  insert all
    into target_table(id, value_1, value_2)
    log errors
    reject limit unlimited
    into target_table_2(id, value_1, value_2)
    log errors
    reject limit unlimited
  select 1,2,3
    from dual
    connect by level <=2
  ;
  dbms_output.put_line('Inserted '|| SQL%ROWCOUNT||' rows');
  dbms_output.put_line(dml_utils.pop_count('ERR$_TARGET_TABLE')||' errors found while inserting into TARGET_TABLE');
  dbms_output.put_line(dml_utils.pop_count('ERR$_TARGET_TABLE_2')||' errors found while inserting into TARGET_TABLE_2');
end;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database