oracle transaction for stored procedure?

[Henry]

My application uses Oracle stored procedure to modify multiple tables.
but OracleClient does not support table-like arrays in one execution,
therefore, I need call many times of the stored procedure. In this
case how can I use transaction to rollback all the executions if there
is error in the middle of calls? What I found is that the previous
succussful calls already commit the modification in Oracle side, my
application-level transaction could not rollback those.

Any help?

[Nicholas Paldino [.NET/C# MVP]]

Re: oracle transaction for stored procedure?

Henry,

I can think of two solutions. The first would be to access the
BeginTransactio n method on the connection class that you are using. You
should be able to call this and begin a transaction on the database which
will be rolled back in the case of an error. This would return an
implementation of IDbTransaction. You can then call Commit or Rollback
depending on what happens.

Also, I believe Oracle is supported in COM+, which you can use to create
a serviced component. You can then set it so that the methods on this
component are transactional, in which case it will be handled automatically.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard. caspershouse.co m

"Henry" <web_poster03@y ahoo.com> wrote in message
news:b6015d6e.0 310131828.6bea8 837@posting.goo gle.com...[color=blue]
> My application uses Oracle stored procedure to modify multiple tables.
> but OracleClient does not support table-like arrays in one execution,
> therefore, I need call many times of the stored procedure. In this
> case how can I use transaction to rollback all the executions if there
> is error in the middle of calls? What I found is that the previous
> succussful calls already commit the modification in Oracle side, my
> application-level transaction could not rollback those.
>
> Any help?[/color]