CODEWITHSUNDEEP
c#oracle-databasestored-procedurestransactionscope
Werner van den Heever
Werner van den Heever

Reputation: 753

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

Upvotes: 6

Views: 32578

Answers (2)

Yahia
Yahia

Reputation: 70369

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.

Upvotes: 9

David Goshadze
David Goshadze

Reputation: 1437

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;

Upvotes: 2

Related Questions