Oracle Transaction

Question

Question

Sergio Vidal · Jul 20, 2018

#Ensemble #Business Operation #Business Process (BPL) #SQL

Hi,

I have a question related with EnsLib.SQL.OutboundAdapter adapter.

Is there a way to open an oracle transaction and do either commit or rollback depending on the query result?

Is posible to keep open an oracle transaction to execute a lot of queries on different methods on the same business operation and do a commit when the business process finished?

Thanks and Best Regards

Sergio Vidal

Discussion (4)0

Log in or sign up to continue

Eduard Lebedyuk · Jul 24, 2018

It would be easier to start and finish the transaction in one method (or rather one request to a BO).

Why not send the request to BO containing a list of UPDATES to perform and BO would automatically wrap them in a transaction, returning either a success or error and the position of failed UPDATE statement? Even better send list of some struct and construct statements in BO.

While workarounds are possible (1 Job for BO + indefinite timeout + FIFO on caller part) it would probably be a bad idea.

0 0

score 0 · Answer 1 · 2018-07-23T03:37:30-04:00

Have you tried setting autocommit off and calling COMMIT/SET TRANSACTION NAME/ROLLBACK manually?

I think that would get the results you need.

score 0 · Answer 2 · 2018-07-24T16:04:52-04:00

Thanks for your answer.

There is posible to use these on differents methods?

I mean

A Business Process "BP1" execute a method in an Operation "OP1" that create a TRANSACTION "TR1"

Then the BP1 execute a second method on the OP1 that execute a query (i.e. UPDATE a table)

Then the BP1 execute a third method on OP1 and execute a COMMIT on TR1

That is posible or I have to open and close the transaction in the same method?

Thanks

score 0 · Answer 3 · 2018-07-24T20:09:06-04:00

Yeah, it would be easier to start and finish the transaction in one request to a BO.

I will do it in that way.

Thanks for your help