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
>
>>Benn,
>>One thing to add here is that you won't be able to specify a specific table to perform the Commit/Rollback on. These commands have session-wide functionality. If you had the following:

>>sqlsetprop(nHandle,'Transaction',2)   && 1-Auto, 2-manual, see help
>>sqlexec(nHandle,"INSERT INTO COUNTRY_ZONE VALUES ('12345','ABC')")
>>sqlexec(nHandle,"INSERT INTO SOME_OTHER_TABLE VALUES ('Kilroy was here')")
>>sqlexec(nHandle,"INSERT INTO SOME_OTHER_TABLE_AGAIN VALUES ('George Washington slept here')")
>>sqlexec(nHandle,"commit/rollback")

>>The other tables would be affected as well.
>>