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...
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.
David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.
