-- table used in the examples
desc demo_txn;
/*
Name  Null?    Type
----- -------- -------------------
TS             TIMESTAMP(6)
VAL   NOT NULL NUMBER
*/

1. Implicit COMMIT on normal SQL*Plus exit

insert into demo_txn(ts,val) values(systimestamp,1);
exit

Reconnect:

select * from demo_txn;
-- one row committed automatically

2. Statement-level atomictiy

insert into demo_txn(ts,val) values(systimestamp,2);
insert into demo_txn(ts,val) values(systimestamp,2); -- unique key violation
exit

After reconnecting:

select * from demo_txn;
-- only the first insert survived; the failing statement was rolled back

3. PL/SQL block without exception handling

create or replace procedure load_rows(p_start number) is
begin
  insert into demo_txn(ts,val) values(systimestamp, p_start);
  insert into demo_txn(ts,val) values(systimestamp, p_start+1);
  insert into demo_txn(ts,val) values(systimestamp, p_start+2);
  insert into demo_txn(ts,val) values(systimestamp, p_start+2); -- duplicate
  commit;
end;
/

exec load_rows(10);
-- ORA-00001: unique constraint violated

select val from demo_txn where val between 10 and 12;
-- rows 10,11,12 are present; the failing insert did **not** undo previous work

4. Swallowing the exception

create or replace procedure load_rows(p_start number) is
begin
  insert into demo_txn(ts,val) values(systimestamp, p_start);
  insert into demo_txn(ts,val) values(systimestamp, p_start+1);
  insert into demo_txn(ts,val) values(systimestamp, p_start+2);
  insert into demo_txn(ts,val) values(systimestamp, p_start+2); -- duplicate
  commit;
exception
  when others then
    dbms_output.put_line(sqlcode||':'||sqlerrm);
end;
/

exec load_rows(20);
-- completes silently

select val from demo_txn where val between 20 and 22;
-- rows 20,21,22 are committed even though an error occurred

5. Adding an explicit ROLLBCAK

create or replace procedure load_rows(p_start number) is
begin
  insert into demo_txn(ts,val) values(systimestamp, p_start);
  insert into demo_txn(ts,val) values(systimestamp, p_start+1);
  insert into demo_txn(ts,val) values(systimestamp, p_start+2);
  insert into demo_txn(ts,val) values(systimestamp, p_start+2); -- duplicate
  commit;
exception
  when others then
    rollback;
    dbms_output.put_line(sqlcode||':'||sqlerrm);
end;
/

exec load_rows(30);
-- completes silently

select val from demo_txn where val between 30 and 32;
-- no rows returned; entire transaction rolled back

6. Re-raising the exception

create or replace procedure load_rows(p_start number) is
begin
  insert into demo_txn(ts,val) values(systimestamp, p_start);
  insert into demo_txn(ts,val) values(systimestamp, p_start+1);
  insert into demo_txn(ts,val) values(systimestamp, p_start+2);
  insert into demo_txn(ts,val) values(systimestamp, p_start+2); -- duplicate
  commit;
exception
  when others then
    rollback;
    raise;
end;
/

exec load_rows(40);
-- ORA-00001: unique constraint violated propagated to caller

select val from demo_txn where val between 40 and 42;
-- no rows returned; transaction rolled back and caller notified

Best practice: when an exception is trapped, either roll back or propagate the error—never swallow it silently.