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.
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
This restriction is lifted in 23c btw.