Is there a way to retrieve the actual record that fails a database constraint when trying to insert in an oracle table (using Oracle Non-spatial writer) instead of the SQL statement?
It seems as if FME only outputs the SQL statement that fails and the oracle error message, but can't seem to find a way to get the actual record that fails a constraint.
The challenge is that FME submits features in transaction blocks, and Oracle (and almost all other relational database) only checks constraints on commit. So when the error occurs, FME has already handed over a large-ish block of features (1000 by default) and therefore cannot immediately tell which feature is causing the error, unless Oracle does it in the error message.
The best practice solution is to import data into a staging schema that does not have any constraints, then manually check for data integrity before moving the data into the production schema (with the contraints).
Alternatively, you can set the writer transaction size to 1, that way you'll know which feature fails. However, it will be a lot slower.