Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Commit and rollback Oracle transactions

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();
        }
    }
  1. The stored Procedure is not committing
  2. Oracle SQL developers autocommit is disabled
  3. When I run the stored procedure in Oracle SQL developers it works (fails - like I have made it and doesn't commit)
  4. Only when running the stored procedure in VS2005 the proc fails, triggers the rollback but doesn't execute it
like image 727
Werner van den Heever Avatar asked Feb 12 '13 11:02

Werner van den Heever


2 Answers

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.

like image 193
Yahia Avatar answered Sep 28 '22 17:09

Yahia


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;
like image 24
David Goshadze Avatar answered Sep 28 '22 18:09

David Goshadze