oracle transaction for stored procedure?

  • Thread starter Thread starter Henry
  • Start date Start date
H

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?
 
Henry,

I can think of two solutions. The first would be to access the
BeginTransaction 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.
 
Back
Top