ORACLE 11g Know Insert Record Details which Failed to insert

Question

I have started auditing insert records by user on failure to any table in my oracle 11g Database. I have used following command to do the same.

AUDIT INSERT ANY TABLE BY SHENA BY ACCESS WHENEVER NOT SUCCESSFUL;

I would like to know whenever the record insert will fail, Can i know what was the records which failed to insert into table.

Where we can see such information. Or if you know any other way of auditing of the same please suggest. One way which i know is to write a trigger on insert. In that trigger handle insert failure EXCEPTION and save those values to some table.

Answer 1

Use SQL Loader Utility with following control file format.

options(skip=1,rows=65534,errors=65534,readsize=16777216,bindsize=16777216)
load data
infile 'c:\users\shena\desktop\1.txt'
badfile 'C:\Users\shena\Desktop\test.bad'
discardfile 'C:\Users\shena\Desktop\test.dsc'
log 'C:\Users\shena\Desktop\test.log'
append
into table ma_basic_bd
fields terminated by '|' optionally enclosed by '"' trailing nullcols
(fs_perm_sec_id, 
"DATE" "to_date(:DATE,'YYYY-MM-DD')", 
adjdate "to_date(:adjdate,'YYYY-MM-DD')", 
currency, 
p_price,  
p_price_open, 
p_price_high, 
p_price_low, 
p_volume)

You are requested to use the conventional path loading so that we can get the rejected(rejected because of datatype mismatch and business rule violation) records in .bad file. Conventional path loading is a default option.

Following URL can be used for the detailed knowledge.

https://youtu.be/eovTBGAc2RI

Total 4 videos are there. Very helpful.