Help with SQL query - inserts with OracleTransaction (from C#/asp.net)

jesbon

Hi,
I need advice how to accomplish example as described below.

I have one table INVOICE_HEADER and one table INVOICE_ITEM. I want to first insert data into table INVOIC_HEADER (data received from SELECT sql query). Then I want to insert data into table INVOICE_ITEM (data received from SELECT sql query). I want that no commit of table INVOICE_HEADER should be done if input data is not correct of some reason. I also want that inserted data into table INVOICE_HEADER not should be commited if insert of data in table INVOICE_ITEM fails of some reason. With other/shorter words I just want that inserts for both tables should be commited if inserts in both tables are successful.

Any kind of advice/hints are highly appreciated! I have made a "search" of how to solve this and think that the best solution is to use OracleTransaction (C#) in some way but I can't get it to work.

Thx in advance!

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Choudhary16y ago
hi friend,

you can achieve it using Transactions in Pl/Sql statement in Oracle :
BEGIN
...
UPDATE accts SET bal = my_bal - debit
WHERE acctno = 7715;
...
UPDATE accts SET bal = my_bal + credit
WHERE acctno = 7720;
COMMIT WORK;
END;
This code is the example of band transaction where account is credited and debited from two accounts. This code will commit only if both table will be saved.
Hope it helps you.
Please mark as answer if it helps you.
0
Sam Hobbs16y ago
Look at the Transaction class in the System.Transactions namespace. Also the following articles.
0