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:

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

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;

<!-- end snippet -->

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

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.