ORA-12838 How to pre-test a query before a delete and insert?

Error: ORA-12838: cannot read/modify an object after modifying it in parallel. Committing the delete defeats the purpose of course.

Is there a way I can use an uncommited delete followed by an insert that can be rolled back in case of an issue during e.g. the insert writes?

Code:

DECLARE tbl_count number; sql_stmt long;
BEGIN SELECT COUNT(*) INTO tbl_count FROM ALL_TABLES WHERE table_name = 'XXX';
IF(tbl_count <= 0) THEN sql_stmt:=
'CREATE TABLE XXX ( AA varchar2(255), BB DATE )';
EXECUTE IMMEDIATE sql_stmt;
END IF;
END;
BEGIN EXECUTE IMMEDIATE 'DELETE FROM XXX';
INSERT INTO XXX
SELECT "AA", TO_DATE("BB",'YYYY-MM-DD') AS BB
FROM "XXX_STG";
COMMIT;
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
END;

It does't yet make sense conceptually, would appreciate some help/ideas.

1 Answer

ORA-12838 is not related to transactions per se, but is about if you are using parallel dml to modify a table. If you do not use parallel DML, then you can DELETE-then-INSERT in the same transaction without any issues.

SQL> create table t as 2 select * from dba_objects;
Table created.
SQL>
SQL> begin 2 delete from t; 3 insert into t 4 select * from dba_objects; 5 commit; 6 end; 7 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> alter session force parallel dml;
Session altered.
SQL> begin 2 delete from t; 3 insert into t 4 select * from dba_objects; 5 commit; 6 end; 7 /
begin
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
ORA-06512: at line 3

If you want to keep the parallel processing, and also keep a record of the deleted rows "just in case", you could do something like

• create backup_table as select * from table
• truncate table
• insert into table select ...

This restriction is lifted in 23c btw.