MQSeries.net :: View topic - Oracle autononomous commits in called stored proc

Rookwood · Posted: Thu Dec 14, 2006 4:55 am Post subject:

Hi all,

have several strange issues with our MB v5.0 (csd 7) and Oracle 9.2i installation on Solaris. No XA coordination involved.

Problem A.
1. Message flow calls Oracle stored proc.
2. Error occurs in stored proc which is caught in pl/sql code and internal pl/sql error handling process writes error info to an Oracle table and runs commit statement. (autonomous commit)
3. Pl/sql error handling then throws exception so broker is notified of a problem and broker throws error and performs rollback of input message.

Expected result of the above is that message is backed out and error data is found in the Oracle table.
Actual result, 99 times out of 100 the actual = expected. 1 time out 100 the data does not appear in the error table until the message flow has been killed.

Problem B
1. Message flow calls Oracle pl/sql procedure.
2. said procedure 'seeds' and table with data, calls commit statement (autononomous commit)
3. pl/sql proc then calls java procedure to utlise seeded table data for further processing.
4. java proc reports that it cannot find said seeded data in the table, i.e it appears that the commit did actually take place.

Heres the nub of the problem: If the Oracle developer calls his stored proc from TOAD using the same userid as the broker uses, passing the same parameters the broker passes, then the java proc is able to see the seeded data; i.e. the commit seems to have been achieved. Java process completes and returns control to the pl/sql proc which completes ok.

In both instances it almost seems that the broker is 'hijacking' the Oracle commit and preventing it from being processed by the database.

Has anyone seen anything similar or are we making naive assumptions about what we'd expect to see happening.
[/list]
_________________
GS