Add support for autonomous blocks in stored routines.
This would allow a stored routine marked with autonomous_transaction to commit
and rollback it's own work, independent of the work of the caller.
Oracle defines this as "PRAGMA AUTONOMOUS_TRANSACTION":
This pragma can be applied to:
- Top-level (not nested) anonymous PL/SQL blocks
- Local, standalone, and packaged functions and procedures
- Methods of a SQL object type
- Database triggers
For the moment a workaround is to use the Aria storage engine for tables that should be persistent against ROLLBACK. Aria tables are never rollbacked
(except if the server crashes in the middle of a query).
The suggested implementation is to run the autonomous routine with it's own THD (== user), like it would be a new independent connection.
An example of an Oracle stored function using AUTONOMOUS_TRANSACTION: