Home > Enterprise >  Oracle - create_error_log - with blob
Oracle - create_error_log - with blob

Time:03-18

Here is small problem, we have table:

CREATE TABLE "SCHEMA_OLD"."DOCUMENT_DATA" (
 "ID" NUMBER(19,0) NOT NULL ENABLE,
 "DATE_ALTERED" TIMESTAMP (6),
 "USER_ALTERED" VARCHAR2(50 CHAR),
 "DATE_CREATED" TIMESTAMP (6),
 "USER_CREATED" VARCHAR2(50 CHAR),
 "VERSION_ID" NUMBER(19,0) NOT NULL ENABLE,
 "DATA" BLOB,
 "IS_MIGR" NUMBER(1,0),
 "MIGRT_KEY" VARCHAR2(60 CHAR)
 CONSTRAINT "data_pk" PRIMARY KEY ("ID")
)

We also implemented oracle err logging, with skipping BLOB column

exec dbms_errlog.create_error_log(dml_table_name => 'schema_old.document_data', skip_unsupported => true);

On the other schema we have identical table schema_new.document_data during copy data, we get error of duplicate primary key "ORA-00001: unique constraint violated" while inserting.

begin

  insert /*  append parallel(8) */ into schema_old.document_data 
  (ID, DATE_ALTERED, USER_ALTERED, DATE_CREATED, USER_CREATED, VERSION_ID, DATA, IS_MIGR, MIGRT_KEY)
  select /*  parallel(8) */ ID, DATE_ALTERED, USER_ALTERED, DATE_CREATED, USER_CREATED, 
                            VERSION_ID, DATA, IS_MIGR, MIGRT_KEY
  from schema_new.document_data
  log errors into schema_old.err$_document_data reject limit unlimited;

exception
  when others then
  schema_old.log#write('Script_document_data ERROR:' || sqlerrm);
  raise;
end;

But table schema_old.err$_document_data is empty after running insert

We made sure that this type of logging works, by removing "Blob" column from both tables, after running insert we get error in table, but how to make it work with "blob" column existing?

CodePudding user response:

You are doing a direct path insert, this is a documented restriction for DML error logging for unique constraint violations

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423

Restrictions on DML Error Logging

The following conditions cause the statement to fail and roll back without invoking the error logging capability:

Violated deferred constraints.

Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.

Any update operation UPDATE or MERGE that raises a unique constraint or index violation.

  • Related