Pragma_autonomous is not asynchronous
I am a Senior Consultant specialized in Oracle APEX and PL/SQL. I am an active member of the nlOUG committee where I help organise events like APEX World, Database Cloud Day and EMEA tour. On APEX World I also organise and present the student track where we try to get the upcoming generation enthousiastic for Oracle APEX.
This article is much shorter than my previous articles, so it’s going to be a quick read.
I recently ran into a strange situation. At the client we have a front-end system which presents forms for clients to fill in. The data is stored in the back-end which also hosts some applications for internal users.
What happened is that the front-end experienced time-outs when sending data to the back-end. So I investigated in the back-end and found a call to procedure which had pragma_autonomous in its declaration. That procedure made some soap calls to another system which took several minutes to complete and send a response back. That resulted in time-outs on the front-end, since it would only wait for 30 seconds maximum.
I started to try to understand why it was set up in this way. So I came to the conclusion that the person who build this must have thought that by adding pragma_autonomous*,* the procedure would fire and the main transaction would just continue. But that is not the case!! And come to think of it, I have seen this before. So I thought I would share with you.
To make things clear, pragma_autonomous is just a way to manipulate data outside of your current session on the database. You can insert, update and delete and commit data without effecting the current state of data in your transaction, the procedure runs in its own (short lived) session on the database. Mind you, your current transaction will wait for the autonomous transaction to finish!
As you understand this, this is the reason why you will always find this in logging packages. You can do your logging outside of your main transaction and commit it, even if your main transaction fails.
If you really want to run procedures asynchronously, you should use dmbs_scheduler and create jobs (which can drop automatically after running). Of course you should always consider carefully if running parallel will give the desired result. Any dependency in data might cause unwanted results and you will have no influence on when the job runs and when it is finished. This is the downside of running procedures in parallel.
Thank you for reading and hope it helps.
