Oracle “Reject” — Overview

In Oracle systems, the word “reject” is used informally to describe different kinds of failures or exclusions.

Conceptually, there are exactly four types of rejection, each occurring at a different layer of the system.

The Four Types of Rejection (High-Level)

I. Oracle engine-level rejection

Rejection detected by the Oracle database engine during SQL or PL/SQL execution.

Examples:

• constraint violations

• trigger errors

• DML execution errors

• privilege or object errors

This category includes DML rejection, which is described in detail below.

II. Business-logic rejection

Intentional rejection defined by application or business rules.

Examples:

• “invalid customer status”

• “negative balance not allowed”

• manual insert into reject table

• status = 'REJECT'

These are not Oracle errors — they are designed outcomes.

III. Integration / loading rejection

Rejection that occurs at the data ingestion boundary, before or during loading.

Examples:

• SQL*Loader .bad records

• External table REJECT LIMIT

• ETL tool error outputs

This happens outside or before relational DML logic.

IV. User / privilege / environment rejection

Rejection caused by missing privileges or invalid runtime environment.

Examples:

• insufficient privileges

• missing objects

• invalid packages or triggers

These rejections are always statement-level and never row-level.

Focus Area: DML Rejection

We now focus on DML rejection, because it is the most subtle, powerful, and frequently misunderstood mechanism.

What DML Rejection Means in Oracle

DML rejection occurs during execution of:

• INSERT

• UPDATE

• DELETE

• MERGE

When Oracle attempts to apply data changes and detects violations or errors.

Traditional behavior:

• Any error aborts the entire statement.

Oracle introduces DML Error Logging to change this behavior.

Oracle DML Error Logging — Definition

Oracle DML Error Logging is a database-engine feature that allows:

• continuation of a DML statement despite row-level errors

• logging of failed rows into a dedicated error table

• full execution in a single set-based SQL statement

Formally:

Oracle classifies row-level DML errors, suppresses them, logs diagnostic data, and allows the statement to continue for remaining rows.

How Oracle Decides: Log vs Raise

Oracle makes a hard internal distinction between row-level errors and statement-level errors.

A. Row-level DML errors — Logged

These are errors that can be attributed to a specific row.

Examples:

• PK / UNIQUE violation (ORA-00001)

• CHECK constraint violation (ORA-02290)

• NOT NULL violation (ORA-01400)

• FK violation (ORA-02291)

• data conversion errors (ORA-06502, ORA-01861)

• trigger RAISE_APPLICATION_ERROR executed per row

Behavior:

• The error is handled internally by the SQL engine.

• Error information is written to ERR$_<table>.

• The row is skipped.

• The statement continues.

• No exception is raised to PL/SQL.

B. Statement-level / fatal errors — Raised, never logged

These are errors that affect the entire statement or execution environment.

Examples:

• insufficient privileges (ORA-01031)

• table not found (ORA-00942)

• invalid trigger

• deadlock

• snapshot too old

• internal errors

Behavior:

• The DML statement is aborted.

• An exception is raised.

• Control passes to the PL/SQL EXCEPTION block, if present.

• No entry is written to the DML reject (ERR$_) table.

Statement-level or fatal errors are always raised as exceptions and are never logged into the DML error log table.

Why Oracle Enforces This Rule

Oracle cannot safely log and continue execution when:

• the execution environment is invalid

• required privileges are missing

• objects or metadata are broken

• transactional consistency is at risk

Therefore:

• such errors are raised immediately

• they are never downgraded to row-level rejects

• they are never written to ERR$_ tables

Error Log Table

The error log table is created using:

This produces a table named ERR$_TARGET_TABLE.

Key columns include:

Oracle DML Error Logging vs Other RDBMS

Oracle (baseline)

Native behavior:

• Row-level error capture is supported.

• The statement continues despite row errors.

• No PL/SQL exception handling is required.

• Error code, message, ROWID, and tag are preserved.

• Supported for INSERT, UPDATE, DELETE, and MERGE.

• There is no per-row rollback; failed rows are skipped.

This is an engine-level feature, not a procedural workaround.

PostgreSQL

PostgreSQL has no native equivalent.

• No row-level DML error logging.

• No “continue on error” capability inside a single SQL statement.

Default behavior:

• One bad row causes the entire statement to fail.

• The entire transaction is aborted.

Typical workarounds:

• Pre-validation (avoids errors but provides no error logging).

• PL/pgSQL row-by-row loops with exception handling (slow and procedural).

MySQL / MariaDB

Support is partial and risky.

• INSERT IGNORE continues execution but silently drops bad rows.

• No reject table is created.

• No error reason is preserved.

• This approach carries a high risk of data corruption.

SQL Server

SQL Server has no native equivalent.

• No LOG ERRORS.

• No row-level continuation in pure SQL.

Closest alternative:

• SSIS, which provides ETL-level error redirection outside the database engine.

Final Comparative Matrix

Key Takeaway

Oracle is the only major RDBMS where row-level DML errors can be logged and skipped natively, safely, and in a fully set-based manner.

All other platforms:

• require procedural loops, or

• require external ETL tools, or

• silently ignore invalid data.