Commit and rollback Oracle transactions

Question

I'm calling this proc in C# from oracle. I made the proc to return an error. In other words the proc fails and pv_error is populated with string and the rollback gets triggered, but doesn't work. I'm not sure why. So, what am I doing wrong? Thanks in advance.

private void hhrcv_update_dc_grs_carton()
    {
        OracleCommand cmd = new OracleCommand();
        cmd.Connection = conn;
        conn.Open();
        OracleTransaction trans = conn.BeginTransaction();
        cmd.CommandTimeout = 0;
        cmd.CommandText = "dc.hhrcv_update_dc_grs_carton";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("pn_dc_grs_no", OracleDbType.Number).Value = txtDcGRSNo.Text;
        cmd.Parameters.Add("pn_pallet_id_no", OracleDbType.Number).Value = txtPalletId.Text;
        cmd.Parameters.Add("pn_carton_code", OracleDbType.VarChar).Value = txtCartonCode.Text;
        cmd.Parameters.Add("pn_company_id_no", OracleDbType.Number).Value = Companyid;
        cmd.Parameters.Add("pn_order_no", OracleDbType.Number).Value = txtOrderno.Text;
        cmd.Parameters.Add("pn_emp_id_no", OracleDbType.Number).Value = empid;
        cmd.Parameters.Add(new OracleParameter("pv_error", OracleDbType.VarChar));
        cmd.Parameters["pv_error"].Direction = ParameterDirection.Output;
        string pv_error;
        cmd.ExecuteNonQuery();
        pv_error = cmd.Parameters["pv_error"].Value.ToString();

        if (pv_error.ToString() == "")
        {
            trans.Commit();
        }
        else
        {
            trans.Rollback();
            MessageBox.Show("" + pv_error, "Error");
            frmReturns r = new frmReturns();
            r.Show();
            this.Hide();
        }
    }

The stored Procedure is not committing
Oracle SQL developers autocommit is disabled
When I run the stored procedure in Oracle SQL developers it works (fails - like I have made it and doesn't commit)
Only when running the stored procedure in VS2005 the proc fails, triggers the rollback but doesn't execute it

What doesn't work? Maybe it's because you're not disposing the transaction. Use a using-statement to make sure it gets disposed. http://msdn.microsoft.com/en-us/library/yh598w02.aspx — Andre Loker, Feb 12 '13 at 11:40
The rollback doesn't work. It still executes the procedure and commits it even though it triggers the Rollback — Werner van den Heever, Feb 12 '13 at 11:43
I can think of two reasons 1. Autocommit 2. Stored procedure commits data itself — David Goshadze, Feb 12 '13 at 11:45
The stored procedure is definitely not committing. You saying oracle auto commits? How can I check this and disable it? — Werner van den Heever, Feb 12 '13 at 11:47
http://www.devart.com/dotconnect/oracle/docs/Devart.Data.Oracle~Devart.Data.Oracle.OracleConnection~AutoCommit.html — David Goshadze, Feb 12 '13 at 12:38

Yahia · Answer 1 · 2013-02-12T14:25:20.317

9

Most likely you need to add:

cmd.Transaction = tran;

after calling BeginTransaction.

Without this the runtime does not know that cmd is part of the transaction tran!

For details see the documentation.

edited Feb 12 '13 at 14:25

answered Feb 12 '13 at 14:20

Yahia

69,653
9
115
144

Did not work for me :-( But even without this, `cmd` and `tran` already know about each other because `tran=conn.BeginTransaction()` and `cmd=new OracleCommand(...sql..., conn)` – Roland Jan 12 '16 at 12:13
the alternative is to create the command after creating the transaction -- the command will automatically participate in the active transaction – Igor Pashchuk Aug 05 '19 at 17:34

score 2 · Answer 2 · answered Feb 12 '13 at 14:11

2

As I posted in comment, I Highly reccommend checking connection's Autocommit property.

As Oracle's documentation states

This property determines if Commit is called for the current transaction after the execution of each SQL statement; otherwise, false. The default value is true.

So at least try

conn.Autocommit=false;

answered Feb 12 '13 at 14:11

David Goshadze

1,439
12
16

Yes thanks. I have tried that and a couple of other possibilities. Unfortunately it doesn't work but I have asked Devart support/forum. Will post their answer here when I get it. – Werner van den Heever Feb 12 '13 at 14:23
5

This probably relates to the Microsoft's `System.Data.OracleClient`, not Oracle's `Oracle.DataAccess.Client` – Roland Jan 12 '16 at 12:19

Commit and rollback Oracle transactions

2 Answers2