LanverseLanverse
LanHubCommunityJobsCompaniesNewsBlogServices
LanverseLanverse

Real IT jobs, company reviews, salary insights, and career discussions from the tech community.

Explore
LanHubIT JobsCompany ReviewsSalary InsightsCommunityTech News
Company
Oracle ServicesPost a JobSubmit ReviewWrite a PostBlog
Connect
About UsMethodologyFeedbackFAQTerms of UsePrivacy Policy
© 2026 Lanverse. All rights reserved.Career research and community insights for IT professionals.
HomeJobsCommunityCompaniesAbout Us
BlogOracle DML Error Logging for Data Loads: DBMS_ERRLOG, LOG ERRORS and Safe Reprocessing
SQL & PL/SQL21 November 2026

Oracle DML Error Logging for Data Loads: DBMS_ERRLOG, LOG ERRORS and Safe Reprocessing

Lanverse
Lanverse TeamOfficial Blog
#Oracle Database#DML Error Logging#DBMS_ERRLOG#LOG ERRORS#MERGE#Data Integration

On this page

Related reads

Suggested Articles

SQL & PL/SQLPL/SQL Bulk Processing Without Losing Failed Rows: BULK COLLECT, FORALL and SAVE EXCEPTIONSBulk PL/SQL can reduce SQL engine round trips, but a fast load is not useful if failed recor…14 November 2026ServiceNowServiceNow Flow Designer Error Handling: Recovery, Subflows and Production TroubleshootingA ServiceNow flow that catches an error has not necessarily completed its business task. Thi…21 November 2026OICOracle Fusion REST API Pagination and Incremental Extraction: A Practical OIC GuideA hands-on, reader-first guide to paging Oracle Fusion REST collections in OIC, safely extra…10 November 2026

A single invalid row should not always force a large Oracle data load to start over. This guide explains when DML error logging helps, how to create and inspect an error table, and how to reconcile accepted and rejected rows without hiding failures. It also covers MERGE caveats, transaction boundaries, reject limits, and practical restart strategies for integration teams.


A large data load can be technically fast and operationally fragile. Imagine an interface that inserts thousands of supplier cost records. Almost every record is valid, but one row violates a target constraint. A conventional INSERT INTO ... SELECT may fail as a statement, leaving the team to investigate the exception and repeat the load. In a production integration, the difficult question is not simply how to keep inserting. It is how to know precisely which records were accepted, which were rejected, and whether a restart will duplicate business transactions.

Oracle Database offers DML error logging through the LOG ERRORS clause and the DBMS_ERRLOG package. It is a useful tool when the business accepts partial success for supported row-level failures. It is not a universal exception handler, not a replacement for source validation, and not a promise that every rejected row can be recovered automatically. This guide develops an illustrative design and shows the checks that should accompany the SQL.

Choose the right failure policy before writing SQL

Start with the business contract. A financial interface may require all records in one control batch to succeed together. A reference-data load may tolerate rejected rows while valid rows continue. Those are different transaction policies. DML error logging is best considered only after the owner of the data agrees that continuing past certain row errors is acceptable. Otherwise the simpler failure-and-rollback behavior can be safer.

A successful SQL statement with rejected rows is not necessarily a successful integration. The database may finish processing the accepted rows, yet the operational control record should indicate partial success, not an unconditional green status. Define whether the downstream process may consume the accepted records immediately, whether errors need manual correction, and whether the same business key may appear in a later retry.

The mechanism is documented in Oracle's Database Administrator's Guide. It applies to supported INSERT, UPDATE, DELETE and MERGE operations, but the exact errors that can be logged depend on statement type and restrictions. Never interpret the availability of LOG ERRORS as permission to ignore database constraints.

Understand what DBMS_ERRLOG actually creates

An error logging table is separate from the application target table. Oracle's DBMS_ERRLOG.CREATE_ERROR_LOG procedure can create one with required control columns and compatible representations of target columns. This saves manual table-definition work, especially when the target has many columns. The error table is not a substitute for a run-control table; it captures row-level DML diagnostics rather than the complete lifecycle of an interface execution.

For a conceptual example, suppose DEMO_COST_TARGET has STAGE_ID, COST_AMOUNT and CURRENCY_CODE, with a unique constraint on STAGE_ID and a check constraint requiring a positive amount. DEMO_COST_STAGE holds incoming rows with a source identifier, a run identifier and a processing state. These are invented demonstration tables, not Oracle Fusion application tables. Their actual DDL, privileges and constraints must be defined in a test schema before running the example.

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG(
    dml_table_name     => 'DEMO_COST_TARGET',
    err_log_table_name => 'ERR$_DEMO_COST_TARGET'
  );
END;
/

The procedure creates a table; it does not enable logging for every statement automatically. Each relevant DML statement must use the appropriate LOG ERRORS clause. If the error table already exists, inspect its structure and ownership instead of blindly recreating it. In managed environments, schema changes should follow the normal deployment and rollback process. A DBA may need to grant the execution account permission to insert into the error table.

Oracle uses control columns such as ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_OPTYP$ and ORA_ERR_TAG$ to describe errors. The error table may also contain mapped target column values. Do not assume that every source attribute will be available there: values not included in the DML target mapping may be absent. For dependable correlation, carry a stable source identifier through the insert mapping whenever the design allows it.

Insert valid rows while recording supported failures

An INSERT ... SELECT with LOG ERRORS can continue after supported row errors rather than aborting the entire statement. The following example uses a fixed tag to demonstrate syntax. In a real interface, generate a unique run-specific tag that is safely passed as a bind value or generated by trusted application logic. The code is illustrative and should not be treated as tested deployment SQL.

INSERT INTO demo_cost_target
  (stage_id, cost_amount, currency_code)
SELECT stage_id, cost_amount, currency_code
  FROM demo_cost_stage
 WHERE run_id = :run_id
LOG ERRORS INTO err$_demo_cost_target (:run_tag)
REJECT LIMIT UNLIMITED;

REJECT LIMIT UNLIMITED is a conscious policy choice, not a harmless default. It allows the statement to continue through eligible errors regardless of their count. A team that wants to stop after an unexpectedly high rejection count should use an appropriate finite limit and test what happens when the threshold is exceeded. The default reject limit is zero if no limit is specified, so leaving the clause out does not mean unlimited tolerance.

The error tag helps separate errors from multiple executions sharing one error table. Make it unique enough for operations, but do not treat it as a security boundary. A tag alone does not establish who owns a record, whether a prior execution committed, or whether a retry is safe. Capture the run identifier in a durable control record with start time, source count, final outcome and a reconciliation result.

Oracle documents the behavior and caveats in its Data Warehousing Guide. The main operational advantage is that the rejected rows can be investigated separately while eligible rows continue. The corresponding risk is that downstream systems might interpret a completed statement as a fully successful business load.

Inspect the error table with the run tag

After the DML, query the error table for the specific execution tag. The numeric Oracle error and message identify the immediate database problem. ORA_ERR_OPTYP$ indicates the DML operation type, and ORA_ERR_ROWID$ may help for operations involving existing target rows. It is not a universal replacement for a source business key. Retain the source identifier and run context separately.

SELECT ora_err_number$,
       ora_err_mesg$,
       ora_err_optyp$,
       ora_err_tag$,
       stage_id,
       cost_amount
  FROM err$_demo_cost_target
 WHERE ora_err_tag$ = :run_tag
 ORDER BY stage_id;

A rejected amount may reflect a target check constraint, a nullability rule, an oversized value or a conversion problem. Record the exact error and examine the input as delivered, not only as transformed for the target. A source string that was converted earlier may have already lost important context. Preserve raw input in staging where permitted by data-retention and privacy rules.

For example, if an amount is invalid, an operator needs to know whether the source supplied a nonnumeric string, the transformation applied an incorrect locale, or the target rejected a negative number. These are different remedies. Automatically retrying the same uncorrected row is unlikely to help a deterministic data error, but it may increase queue noise and obscure the original cause.

Reconcile selected, inserted and rejected records

The most important operational check is a reconciliation, not merely a count of rows in the error table. Record the number of source records selected for the run, the number of target changes accepted, and the number of rejected attempts. For simple one-to-one inserts without additional filters, the accepted and rejected counts should explain the selected count. More complicated joins, duplicate source keys or conditional MERGE branches require a more careful model.

Be precise about what SQL%ROWCOUNT measures in your actual statement and session. It reflects rows affected by the most recent SQL operation, not necessarily all source rows read. A control record should capture the count immediately after the DML if it is used. Do not execute another SQL statement first and then assume the cursor attribute still describes the load.

A useful run-control record has a run ID, extraction watermark, input count, attempted count, accepted count, rejected count, processing state, start and end timestamps, and a concise error summary. Keep error records associated with that run. If accepted rows are committed but the control record is not, the system can report a misleading failure and encourage an unsafe restart. Plan the transaction boundary around this possibility.

A reconciliation query is a diagnostic, not a business guarantee

SELECT COUNT(*) AS rejected_count
  FROM err$_demo_cost_target
 WHERE ora_err_tag$ = :run_tag;

The query counts recorded error rows for one tag. It does not prove that every attempted source record was either inserted or rejected, because statement-level failures and unsupported error cases can interrupt processing. Nor does it prove that the transaction committed. Compare the count with the control record and with actual target keys before declaring the interface complete.

Know the errors that LOG ERRORS will not rescue

The error logging feature has documented limitations. Certain constraint and DML modes can still cause the statement to fail. For example, deferred constraint violations and some unique-constraint errors in direct-path operations are not handled as ordinary logged row errors. Unique violations in certain UPDATE or MERGE paths also have restrictions. Consult the precise Oracle SQL Language Reference for the database version and DML operation in use.

The error table also has datatype limitations. LONG, LOB and object-type values cannot simply be captured as if they were ordinary character columns. DBMS_ERRLOG supports an option to skip unsupported columns during table creation, but skipping a column means the rejected-row record will not contain that value. If the missing value is critical for troubleshooting, retain a separate source record or staging key.

A statement that fails after exceeding a finite reject limit has different recovery implications from a statement that completes with logged errors. Do not assume that the presence of error rows means the target DML was committed. The application must handle the exception, transaction state and reconciliation explicitly. Test the behavior with your database version, including the effect of rollback on the logging records.

Another important limitation is that SQL-level error logging cannot fix business validation rules that were never implemented in the database. If a supplier number is syntactically valid but maps to the wrong ledger, the row might insert successfully. Such defects require domain-specific validation before or after loading. Database constraints protect only the rules they actually express.

MERGE introduces a different set of decisions

MERGE is attractive for restartable interfaces because it can match target keys and choose an update or insert action. However, an upsert is not automatically idempotent. Updating a financial amount on every retry may overwrite a later correction. Updating a timestamp or audit field may cause downstream change detection even when the business data did not change. Define exactly which fields may be updated and under what source-version conditions.

Oracle describes MERGE as deterministic: the same target row cannot be updated multiple times within one MERGE statement. If the source contains multiple rows for the same target key, deduplicate or resolve them before MERGE. Do not rely on LOG ERRORS to transform ambiguous business input into a stable winner. The official MERGE reference explains its matching, update, insert and error-logging clauses.

MERGE INTO demo_cost_target t
USING (
  SELECT stage_id, cost_amount, currency_code
    FROM demo_cost_stage
   WHERE run_id = :run_id
) s
ON (t.stage_id = s.stage_id)
WHEN MATCHED THEN
  UPDATE SET t.cost_amount = s.cost_amount,
             t.currency_code = s.currency_code
WHEN NOT MATCHED THEN
  INSERT (stage_id, cost_amount, currency_code)
  VALUES (s.stage_id, s.cost_amount, s.currency_code)
LOG ERRORS INTO err$_demo_cost_target (:run_tag)
REJECT LIMIT UNLIMITED;

This example deliberately leaves out source deduplication and version checks so that the design concern is visible. It is not suitable for production without those controls. A real interface may need to select the latest approved source version, reject ambiguous duplicates and prevent updates to finalized transactions. MERGE also has restrictions on updating columns referenced in its ON condition.

Choose between INSERT-only, MERGE and explicit UPDATE plus INSERT based on the business semantics. A reference-data synchronization may legitimately update an existing row. A posted accounting event generally needs a much stricter correction process. Error logging does not decide which changes are permitted; the integration contract must do that.

Build a safe retry queue instead of replaying everything

A common mistake is to reprocess the entire original file after any error. If accepted rows were committed, replaying them may produce duplicate-key errors or unintended updates. Instead, distinguish accepted, rejected, pending and uncertain records. A rejected record should be retried only after its source issue is corrected or the relevant target condition changes. An uncertain record requires reconciliation against the target before replay.

Keep a stable business key and, where necessary, a source event identifier. A technical run ID identifies one attempt; it should not become the only deduplication key because retries intentionally use new attempts. A target unique constraint can prevent duplicate inserts, but a duplicate-key exception by itself does not establish that the existing row contains the correct business values.

For partial-success loads, consider recording a normalized outcome for each source record. The error table supplies database diagnostics, while the application outcome table links source IDs to accepted, rejected or deferred states. These two data sets serve different purposes. Avoid writing an outcome of success merely because the SQL statement returned without an exception.

Think through the crash windows

Suppose the target insert succeeds, but the process crashes before the staging status is updated. If both changes are in the same uncommitted transaction, rollback can restore consistency. If they were committed separately, the next run needs to detect the target record and reconcile its values before deciding what to do. Similarly, a crash after commit but before sending an external acknowledgment may cause the upstream system to resend the same payload.

These are not edge cases to dismiss as rare. They are the reason integration design needs explicit idempotency rules, transaction ownership and a recovery procedure. Write down what the next run does after a crash at each boundary. If the answer is simply to rerun everything, the design is not yet operationally complete.

Use instrumentation that support teams can actually follow

Database diagnostics become easier when an interface registers its module and current action. Oracle's DBMS_APPLICATION_INFO documentation describes SET_MODULE and SET_ACTION for tracking database work. Use descriptive, stable names rather than stuffing large payloads or sensitive values into session metadata.

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'COST_LOAD',
    action_name => 'INSERT_STAGE'
  );
  -- Execute the load and reconciliation here.
  DBMS_APPLICATION_INFO.SET_ACTION('RECONCILE');
END;
/

In a production package, clear or reset the action when processing finishes, including exception paths where appropriate. Correlate the database module with the integration instance ID and run-control record. The purpose is to help an operator move from a failed scheduled job to the relevant SQL activity and source batch without guessing which session executed the work.

Log useful counts and identifiers, not full financial or personal payloads by default. Error tables may contain sensitive column values, so apply the same access control, retention and cleanup practices used for staging data. A convenient troubleshooting table should not become an ungoverned archive of rejected records.

A controlled test plan before deployment

Create a small test batch that includes clean rows, a null value in a required field, a duplicate target key and a value that violates a check constraint. Add a conversion failure only where the target mapping genuinely exercises that conversion. Run the same batch with and without LOG ERRORS and compare statement outcomes, error-table entries and target rows. These tests reveal which failures are supported in your exact environment.

Next, test a finite reject limit and an unlimited limit. Confirm whether the caller receives an exception, what the error table contains and whether your control record reports the correct status. Explicitly issue rollback in one test and commit in another. Inspect both target and error tables afterward; do not infer transactional behavior from a message printed by a client tool.

Test repeated runs with the same business keys. For INSERT-only processing, verify that duplicates are rejected or intentionally skipped according to the contract. For MERGE, verify that the correct source version wins and that unrelated target columns remain unchanged. Include a case where two source rows compete for one target key and ensure the integration rejects ambiguity before the DML.

Finally, simulate failure between processing phases. If your job uses a control table, target DML, staging status updates and an external acknowledgment, test interruptions around each boundary. The outcome should be documented well enough that an on-call engineer can decide whether to resume, reconcile or escalate without modifying production data by trial and error.

When a different approach is better

LOG ERRORS is not always the right mechanism. If the load is truly atomic, ordinary statement failure with a clear rollback may be preferable. If complex procedural validation is required per row, PL/SQL collections and FORALL SAVE EXCEPTIONS can be appropriate, provided the program captures failing source indices and handles partial success carefully. If the volume is small and business errors must be returned immediately to an API caller, explicit validation and a structured response may be easier to operate.

For high-volume ingestion, consider the execution plan, target indexes, partitioning strategy, undo and redo behavior, and the cost of writing error records. Error logging is primarily a correctness and recoverability feature, not a guarantee of faster execution. Benchmark the real workload rather than assuming that accepting partial failures will reduce elapsed time.

Do not confuse the database error log with the functional error report. A constraint failure tells you what the database rejected. The business user may need a clearer explanation, such as which source file row to correct and which valid values are allowed. Translate technical diagnostics into actionable operational messages while retaining the original Oracle error for investigation.

A practical production support checklist

  1. Identify the exact run ID, source extraction boundary and error tag.
  2. Verify the input count and check whether source filtering or joins changed the attempted row count.
  3. Inspect the error table by tag and preserve the source business keys for every rejected row.
  4. Check whether the SQL statement completed, hit a reject limit or failed for an unsupported reason.
  5. Confirm the transaction outcome and reconcile accepted target keys against source and control records.
  6. Classify rejected records as correctable data issues, configuration problems or infrastructure incidents.
  7. Reprocess only records whose business state and deduplication rules make retry safe.
  8. Close the run with an accurate status: success, partial success or failure, with evidence for each count.

The checklist is intentionally operational. It prevents a common anti-pattern: celebrating that a job did not raise an exception while silently leaving rejected transactions unaddressed. An interface is complete only when its data outcome can be explained and its next action is clear.

Final design principle

Oracle DML error logging is valuable because it separates certain row-level failures from the fate of an entire DML statement. That flexibility is useful only when paired with a clear partial-success policy, stable source keys, a run-specific error tag, reconciliation and deliberate transaction boundaries. Treat the error table as evidence, not as the whole processing state.

Before adopting this pattern, review the vendor documentation for the precise database release and statement form you use, especially MERGE and constraint restrictions. Validate every illustrative SQL fragment in a nonproduction schema, then have the application owner approve how rejected records are handled. The best integration is not the one that hides the most errors; it is the one that makes every accepted and rejected record accountable.

Career intelligence

Explore companies & careers

Research company profiles, jobs, salaries and employee reviews.

Browse all companies ↗
  • H

    HCL TechCompany profile ↗

    JobsSalariesReviews
  • C

    CognizantCompany profile ↗

    JobsSalariesReviews
  • W

    WiproCompany profile ↗

    JobsSalariesReviews
  • N

    NagarroCompany profile ↗

    JobsSalariesReviews
  • C

    CitiCompany profile ↗

    JobsSalariesReviews
  • D

    DeloitteCompany profile ↗

    JobsSalariesReviews
  • A

    AccentureCompany profile ↗

    JobsSalariesReviews
  • O

    OracleCompany profile ↗

    JobsSalariesReviews

Explore company research and career paths across Lanverse.

Explore jobs →

Research & references

Article sources

References linked by the author of this article. External links open in a new tab.

  1. 01docs.oracle.comdocs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html↗
  2. 02docs.oracle.comdocs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html↗
  3. 03docs.oracle.comdocs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/database-data-warehousing-guide.pdf↗
  4. 04docs.oracle.comdocs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_APPLICATION_INFO.html↗
Explore LanverseCompaniesReviewsJobsCompare
More Articles
Share