Write multiple tables to Oracle database in a single transaction (via ROracle)

Is it possible to write multiple R dataframes to an Oracle database in a single transaction via ROracle/DBI and dbWriteTable ?

dbWriteTable commits the table in full, so transaction management via dbCommit and dbRollback does not help me here as far as I can tell.

I need a way to write multiple tables at once in a single transaction, so if one fails, all of them are rolled back.

Thanks in advance !

So, the solution I arrived at was to use reticulate.

If someone has a pure R solution that follows a similar pattern, I would still be interested in hearing it and changing the accepted solution.

reticulate::py_require("polars[database]")
reticulate::py_require("sqlalchemy")

polars     <- reticulate::import("polars")
sqlalchemy <- reticulate::import("sqlalchemy")

engine    <- sqlalchemy$create_engine("sqlite:///transactions.sqlite3", future = TRUE)
dataframe <- polars$DataFrame(data.frame(x = 1:5, y = letters[1:5]))

with(
    engine$begin() %as% conn, 
    {
        dataframe$write_database("table_a", conn, if_table_exists = "append")
        dataframe$write_database("table_b", conn, if_table_exists = "append")
        dataframe$write_database("table_c", conn, if_table_exists = "append")
        stop("OOPS :(")
    }
)

Note: there was a bug in with() which the maintainers were kind enough to fix within a day, and this now works (i.e. the whole transaction is rolled-back upon error) with the latest branch.