Re: using Autonomous transaction

shyamp73_at_yahoo.com (Sam) wrote in message news:<564714ef.0308110755.31576624_at_posting.google.com>...
> Guys,
> I have a question regarding oracle.
>
> I have a stored procedure executing a number of statements including
> delete , select and lots of inserts in one big procedure and main
> transaction
>
> I have a stored procedure sp_log_action with a PRAGMA AUTONOMOUS
> TRANSACTION defined which is called after each statement in the main
> stored procedure(this writes the event to a table).

After every statement? You really need that detailed of a trace?
>
> When i run with one session, the main procedure, it runs as expected
> i.e even if there is a error in the main stored Proc, it logs an
> error.
>
> But when i run from multiple sessions , it returns me a "deadlock
> detected while waiting for resource" error.
>

> It seems to me a locking problem. can u suggest me a way around this.
> Or am I doing the right thing to log a event . ???
> Any suggestions will be helpful!!
>
> thanks,
> Shyam Pillai

Is it the called procedure or something else? have you tried removing the calls to that Proc? What is the specific error message?

Details, Man, we want details! 8^) Received on Tue Aug 12 2003 - 19:21:20 CEST