Create oracle table with auto commit on

It's possible using an Autonomous Transaction.

CREATE TABLE t1 (x INTEGER PRIMARY KEY);CREATE TABLE t2 (x INTEGER);CREATE TABLE t3 (x INTEGER);CREATE TABLE t4 (x INTEGER REFERENCES t1(x));

Then run this anononymous PL/SQL. Look carefully, the local procedure upd_table_3 isn't run when it's declared. It's run when called within the main BEGIN block:

DECLARE   PROCEDURE upd_table_3 IS  PRAGMA AUTONOMOUS_TRANSACTION;  BEGIN    INSERT INTO t3 VALUES (3);    COMMIT;  END;BEGIN  INSERT INTO t1 VALUES (1);  INSERT INTO t2 VALUES (2);  upd_table_3;  INSERT INTO t4 VALUES (4);  COMMIT;END;/

The procedure should fail intentionally on the 4th insert with ORA-02291. I made it this way. Then

ROLLBACK;

All the tables should be rolled back except t3, which we did the insert in an autonomous transaction.

SELECT * FROM t1;no rows selectedSELECT * FROM t2;no rows selectedSELECT * FROM t3;         X----------         3SELECT * FROM t4;no rows selected

From your post explanation what I understood is #3 insert has no dependency on other insert and you anyways want to commit it. In that case you can have them in different transaction block like.

begin transaction1 Insert into table12 Delete from table24 Insert into table4Commit;ExceptionRollback;

Second Part

begin transaction3 Insert into Table3commitOn Exception Rollback;

(OR) using a save point like below, that way #3 insert will not be rolled back.

BEGIN  SAVEPOINT startpoint;3 Insert into Table3;  SAVEPOINT startpoint2;1 Insert into table1;2 Delete from table2;4 Insert into table4;EXCEPTION  WHEN SOMETHING THEN    ROLLBACK TO startpoint2;    RAISE;END;

PS: Syntax may not be proper; So consult the documentation. SAVEPOINT idea taken from BEGIN - END block atomic transactions in PL/SQL

There is nothing to do. Take your script:

1 Insert into table12 Delete from table23 Insert into Table34 Insert into table4Commit;

Assume you get an exception in statement 4, then statement 4 is actually not executed thus it does not make sense to rollback statement 4. A simple COMMIT will commit only statements 1,2,3 in this case.

However, it looks different when you get an exception at statement 3, for example (while statement 4 runs fine). Do you like to rollback to statement 2 in this case, i.e. rollback also statement 4? Then you have to work with SAVEPOINTS as mentioned in the other answers.