Paul,
I'm not sure how OAS works. If it uses a separate session context for each request then the pragma instruction will be of no use. It won't hurt anything, it simply adds nothing. Sorry I can't be of more help here.
IAC, here is the syntax for the pragma instruction:
create or replace procedure sp_example is
pragma AUTONOMOUS_TRANSACTION
/* variable declarations */
begin
/* do some processing */
end;
I put the compiler instruction before anything in the declaration block.
HTH.
>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
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao