INSERT, Multi-Table INSERT, UPDATE, DELETE, MERGE, RETURNING, and Error Logging

Learning outcomes

ServiceHub receives work-order changes from APIs, batch files, and technicians in the field. A careless mass update can close every ticket, two source rows can make one MERGE target ambiguous, and one malformed batch row can abort an otherwise useful load. Oracle DML is not merely syntax for changing rows; it is transaction-scoped state change with locking, redo/undo, privilege, and determinism consequences.

1. DML changes rows inside a transaction

INSERT, UPDATE, DELETE, and MERGE acquire transactional locks and generate undo/redo as required by the operation. Until commit, your session can see its own changes while other sessions normally see only committed versions. A statement error usually rolls back that statement, not every successful statement already issued in the transaction.

DROP TABLE sh07_dml_stage IF EXISTS PURGE;DROP TABLE sh07_dml_target IF EXISTS PURGE;CREATE TABLE sh07_dml_target (    work_order_id NUMBER PRIMARY KEY,    external_key  VARCHAR2(30) NOT NULL UNIQUE,    status_code   VARCHAR2(12) NOT NULL                  CHECK (status_code IN ('OPEN','HOLD','CLOSED')),    amount        NUMBER(10,2) NOT NULL);CREATE TABLE sh07_dml_stage (    source_seq    NUMBER PRIMARY KEY,    external_key  VARCHAR2(30),    status_code   VARCHAR2(12),    amount        NUMBER(10,2));INSERT INTO sh07_dml_target VALUES (101,'WO-101','OPEN',250);INSERT INTO sh07_dml_target VALUES (102,'WO-102','HOLD',400);COMMIT;

2. INSERT and multi-table INSERT express different routing jobs

A conventional single-table insert adds rows to one target. Oracle multi-table insert can route one source query to several targets with INSERT ALL or conditionally with WHEN clauses. It is useful for relational routing, not a replacement for transactions or validation.

DROP TABLE sh07_open_audit IF EXISTS PURGE;DROP TABLE sh07_closed_audit IF EXISTS PURGE;CREATE TABLE sh07_open_audit   (external_key VARCHAR2(30), amount NUMBER(10,2));CREATE TABLE sh07_closed_audit (external_key VARCHAR2(30), amount NUMBER(10,2));INSERT ALL  WHEN status_code = 'OPEN' THEN    INTO sh07_open_audit(external_key, amount) VALUES(external_key, amount)  WHEN status_code = 'CLOSED' THEN    INTO sh07_closed_audit(external_key, amount) VALUES(external_key, amount)SELECT external_key, status_code, amountFROM sh07_dml_target;ROLLBACK;

Current Oracle restrictions matter: a multi-table insert does not support the ordinary RETURNING clause. If the application needs generated values back per row, choose a different DML shape rather than assuming every INSERT form has identical capabilities.

3. Mass UPDATE/DELETE: preview the row set first

The dangerous statement is often syntactically valid. Omitting WHERE from UPDATE or DELETE targets every eligible row. In a disposable lab, a savepoint makes the risk observable without keeping the damage.

SAVEPOINT before_mass_change;UPDATE sh07_dml_targetSET status_code = 'CLOSED';-- Every row was updated. This is valid SQL, but the business intent was one ticket.SELECT work_order_id, status_codeFROM sh07_dml_targetORDER BY work_order_id;ROLLBACK TO before_mass_change;-- Preview the intended key before changing it.SELECT work_order_id, external_key, status_codeFROM sh07_dml_targetWHERE external_key = 'WO-101';UPDATE sh07_dml_targetSET status_code = 'CLOSED'WHERE external_key = 'WO-101';ROLLBACK;

In production, add application guardrails such as expected-row-count checks, bounded batches, and transaction rollback on mismatch. Do not depend on a human noticing “2 rows updated” after an accidental mass change has already been committed.

4. MERGE is deterministic: make the source unique per target key

Oracle defines MERGE as deterministic: the same target row cannot be updated more than once by one MERGE statement. A staging set with multiple rows mapping to one existing target key is therefore not a harmless duplicate—it can make the statement unstable and raise ORA-30926.

TRUNCATE TABLE sh07_dml_stage;INSERT INTO sh07_dml_stage VALUES (1,'WO-101','HOLD',260);INSERT INTO sh07_dml_stage VALUES (2,'WO-101','CLOSED',270);COMMIT;MERGE INTO sh07_dml_target tUSING sh07_dml_stage sON (t.external_key = s.external_key)WHEN MATCHED THEN UPDATE SET    t.status_code = s.status_code,    t.amount = s.amountWHEN NOT MATCHED THEN INSERT    (work_order_id, external_key, status_code, amount)VALUES    (1000 + s.source_seq, s.external_key, s.status_code, s.amount);-- Ambiguous source-to-target mapping can raise ORA-30926.

The safe repair is to define the source winner explicitly. “Whichever row Oracle happens to read last” is not a business rule.

MERGE INTO sh07_dml_target tUSING (    SELECT external_key, status_code, amount, source_seq    FROM (        SELECT s.*,               ROW_NUMBER() OVER (                   PARTITION BY external_key                   ORDER BY source_seq DESC               ) AS rn        FROM sh07_dml_stage s    )    WHERE rn = 1) sON (t.external_key = s.external_key)WHEN MATCHED THEN UPDATE SET    t.status_code = s.status_code,    t.amount = s.amountWHEN NOT MATCHED THEN INSERT    (work_order_id, external_key, status_code, amount)VALUES    (1000 + s.source_seq, s.external_key, s.status_code, s.amount);ROLLBACK;

5. RETURNING reduces round trips

For eligible DML, RETURNING ... INTO can return values from changed rows directly to host or PL/SQL variables. This is useful for generated identifiers, timestamps, or before/after values when the SQL form supports them.

SET SERVEROUTPUT ONDECLARE    l_id     NUMBER;    l_status VARCHAR2(12);BEGIN    INSERT INTO sh07_dml_target(work_order_id, external_key, status_code, amount)    VALUES (103,'WO-103','OPEN',175)    RETURNING work_order_id, status_code INTO l_id, l_status;    DBMS_OUTPUT.PUT_LINE('id=' || l_id || ', status=' || l_status);    ROLLBACK;END;/

Do not assume RETURNING works with every DML variant: current documentation excludes cases such as multi-table inserts, parallel DML, and remote objects. Match the feature to the actual statement shape and driver binding model.

6. DML error logging keeps acceptable rows moving—but preserves evidence

DBMS_ERRLOG.CREATE_ERROR_LOG creates a reject table containing diagnostic columns such as ORA_ERR_NUMBER$, ORA_ERR_MESG$, and ORA_ERR_TAG$. The LOG ERRORS clause can record supported row-level failures while allowing other rows to succeed.

BEGIN    DBMS_ERRLOG.CREATE_ERROR_LOG('SH07_DML_TARGET');EXCEPTION    WHEN OTHERS THEN        IF SQLCODE != -955 THEN RAISE; END IF;END;/TRUNCATE TABLE sh07_dml_stage;INSERT INTO sh07_dml_stage VALUES (10,'WO-110','OPEN',100);INSERT INTO sh07_dml_stage VALUES (11,'WO-111','BROKEN',200);INSERT INTO sh07_dml_stage VALUES (12,'WO-112','CLOSED',300);COMMIT;INSERT INTO sh07_dml_target(work_order_id, external_key, status_code, amount)SELECT 1000 + source_seq, external_key, status_code, amountFROM sh07_dml_stageLOG ERRORS INTO err$_sh07_dml_target ('LOAD-20260825')REJECT LIMIT UNLIMITED;SELECT external_key, status_codeFROM sh07_dml_targetWHERE external_key LIKE 'WO-11%'ORDER BY external_key;SELECT ora_err_number$, ora_err_mesg$, ora_err_tag$, external_key, status_codeFROM err$_sh07_dml_target;ROLLBACK;

Error logging is not universal. Some errors terminate the statement, and logged rejects still need ownership, review, retention, and remediation. A production loader should fail the overall job when the reject rate or error classes exceed an explicit policy.

7. Cleanup and production judgment

DROP TABLE err$_sh07_dml_target IF EXISTS PURGE;DROP TABLE sh07_open_audit IF EXISTS PURGE;DROP TABLE sh07_closed_audit IF EXISTS PURGE;DROP TABLE sh07_dml_stage IF EXISTS PURGE;DROP TABLE sh07_dml_target IF EXISTS PURGE;

Use DML only after defining target grain, predicates, concurrency expectations, and rollback. Deduplicate MERGE sources by business rule, not physical row order. Treat error tables as production data with access control and lifecycle rules. These operations require ordinary object privileges; no licensed pack or special COMPATIBLE change is required on the declared 26ai baseline.

8. Summary and next step

Safe Oracle DML means more than “the statement ran”: the affected set is verified, MERGE input is deterministic, returned values come from supported forms, row rejects are inspected, and the transaction is still under deliberate control. Lesson 2 now defines exactly where that transaction begins and ends.

Authoritative references

• INSERT — multi-table INSERT, RETURNING, and error logging rules
• UPDATE — predicate, RETURNING, and wait behavior
• DELETE — DELETE semantics and RETURNING restrictions
• MERGE — deterministic MERGE and privileges
• Managing Tables — Error Logging — DML error logging restrictions and caveats