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?
You are doing a direct path insert, this is a documented restriction for DML error logging for unique constraint violations
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.