ORA-01086: savepoint \'TRAN\' never established in this session or is invalid

[dfrese]

When a Ecto.Repo.transaction fails, I often see the above error in the logs (with Oracle 19c)

I changed the start/abort transaction statements in jamdb_oracle.ex from

        statement = "SAVEPOINT tran"
...
        statement = "ROLLBACK TO tran"

to

        statement = "SET TRANSACTION READ WRITE"
...
        statement = "ROLLBACK"

I don't know enough about Oracle to say if this is equivalent, or 'better', but at least it prevented the above error in the logs. (I only see the actual error that caused the transacton to abort as a ConnectionError exception)

[vstavskyi]

Check connection parameters parameters: [autocommit: 0,...

[dfrese]

Thanks, that prevents the error log too.

But autocommit=0 affects other statements that are not run in a Ecto.Repo.transaction then; I could just wrap everything in my app in a transaction, but...

In migrations; it seems that the version of the last migration does not get comitted to schema_migrations then. I changed 'supports_ddl_transaction?' to true, which fixed that; although it's "wrong". I didn't see how to use autocommit=1 for migrations only, but not otherwise.

Seems really gnarly to get Oracle's transaction semantics in line with Ecto's. I looked into something like executing "SET AUTOCOMMIT OFF" to begin a transaction; which sounds right; but that is not an Oracle-SQL command ("only Sql Plus").

[vstavskyi]

Use Repo.query("COMOFF") and Repo.query("COMON") when you need

See jamdb_oracle_conn.erl L115-L126

31