Calling Oracle autonomous stored procedure from trigger

I have an Oracle trigger which is calling a stored procedure that has PRAGMA AUTONOMOUS开发者_如何学Python_TRANSACTION defined. The values that are passed from the trigger have been committed already but it appears that the values are not available in the stored procedure? I'm not positive of this since the ability to debug/log/commit is difficult and the timing of the output is confusing me a bit. I'd like to know if it's expected that any passed values are simply available in the stored procedure regardless of the AUTONOMOUS_TRANSACTION? Thanks

Values passed in to a stored procedure as parameters will always be available to the stored procedure. It doesn't matter whether the procedure is declared using an autonomous transaction.

Code running in an autonomous transaction cannot see changes made by the calling transaction. 9 times out of 10, when people are describing problems seeing the data they expect, this is the source of the problem.

If your stored procedure is doing anything other than writing something to a log table, I would be exceptionally cautious about using autonomous transactions. If you are using autonomous transactions for anything other than logging, you are almost certainly using them incorrectly. And you are probably introducing a whole host of bugs related to race conditions and transactional integrity.

"The trigger logic is conditionally updating Table B which calls the stored procedure to select from the values on Table A so that Table B can be updated with a calculated value. "

Perhaps Table B really ought to be a Materialized View derived from Table A? We can build a lot of complexity into the WHERE clauses of the queries which populate MViews. Find out more.

If you have a row level trigger on table_x, then that trigger can be fired multiple times by the same statement as different rows are impacted by that statement.

The order in which those rows are impacted is indeterminate. As such, the state of table_x is indeterminate during the execution of a row level trigger. This is why the MUTATING TABLE exception is raised.

An autonomous transaction 'cheats' by looking at the committed state of the table (ie excluding all changes made by that statement, and other statements in the transaction).

If you want a stored procedure to look at the state of table_x in response to activity on that table, then it needs to be done after all the rows changes have been made (ie in a statement level trigger, not a row level trigger).

The design pattern for this is often to set a flag (package level variable) in a row level trigger, check the flag in an AFTER statement level trigger, and if necessary action it and reset it.