I have a ETL pipeline. I want to do delete from table; insert into table; commit; but the oracle delete snap has a implied commit after it so I get delete; commit; insert; commit. My table is sitting there blank with no records until all the inserting is finished because of the implied commit after the oracle delete snap that I cannot turn off?
This is not surprising.
Oracle databases scope a transaction to a single database connection (unless you are using XA…). In order for a pipeline to group multiple snap operations into a single database transaction, the snaps would need to somehow pass the database connection along from snap to snap for each pipeline invocation (e.g., by using a pipeline-instance-aware connection pool) or use XA transactions.