Hi Larry,

Thanks for the note.

I'm using Web Connection as a front end for Oracle through the Oracle Application Server (OAS). All the web users go through one Oracle user account which is in the DAD (Data Access Descriptor) on the OAS. All insert and update statements are handled in Oracle SPs. The stored procedures are complete in that there is a commit or rollback in every DML SP.

I was concerned that if one web user issued a SP with a rollback, that it would affect a transaction in another SP from a different Web user.

My Oracle DBA on this project thinks that the PRAGMA AUTONOMOUS_TRANSACTION statement is not necessary (although it probably wouldn't hurt) since two stored procedures cannot happen at once in one user session. (Or can they??)

Where does the PRAGMA AUTONOMOUS_TRANSACTION go in the SP?

Paul


>Hi Paul,
>No I don't think so.
>
>Oracle doesn't have a BEGIN TRANSACTION command. When you perform any Data Manipulation Language (DML) command, Oracle starts a transaction. That transaction is then active until you do one of the following:
>
>1. Explicitly issue a Commit/Rollback
>2. Issue some Data Definition Language (DDL) command - Oracle performs an implicit COMMIT when you do this
>
>Any DML statements after the initial one are covered under the initial transaction.
>
>SPs are slightly different in that you can add a compiler directive that will cause Oracle to open another transaction within the SP. If you add PRAGMA AUTONOMOUS_TRANSACTION somewhere near the top of your SP, Oracle will create a new transaction context while in the SP. If you perform a DML command, a completely separate transaction is created. Any Commit/Rollback issued within it will only affect the rows in the new transaction.
>
>But this means that the Commit/Rollback must be performed in the context of the SP. You can not use SPT to execute an SP and then use another SQLExec command to issue a Commit/Rollback. The transaction context of the SP is already gone at that point.
>
>HTH.
>
>>Hi Larry,
>>
>>Would putting the sql statements in an Oracle stored procedure restrict the rollback to the one table?
>>
>>Paul
>>