SET TRANSACTION error , C# , Oracle

Question

I have procedure in my C# device application app. This is how it looks:

private void stk_crane_start_movement()
    {
        conn.Open();
        OracleCommand cmd = new OracleCommand();
        OracleTransaction trans;
        trans = conn.BeginTransaction();
        cmd.Transaction = trans;
        cmd.Connection = conn;
        conn.AutoCommit = false;
        cmd.CommandTimeout = 0;
        cmd.CommandText = "dc.stk_crane_start_movement";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("pn_crane_opr_id_no", OracleDbType.Number).Value = empid.ToString();
        cmd.Parameters.Add("pn_crane_movement_id_no", OracleDbType.Number).Value = pn_crane_movement_id_no.ToString();
        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();
            trans.Dispose();
            conn.Close();
            cmd.Dispose();
        }
        else
        {
            trans.Rollback();
            MessageBox.Show("" + pv_error, "Error");
        }
    }

I'm getting ORA-01453: SET TRANSACTION must be first statement of transaction at trans = conn.BeginTransaction();

Can someone please explain to me what exactly i'm doing wrong?

I have also tried it like this:

private void stk_crane_start_movement()
    {
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        OracleTransaction trans;
        trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.Transaction = trans;
        try
        {
            cmd.CommandText = "dc.stk_crane_start_movement";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("pn_crane_opr_id_no", OracleDbType.Number).Value = empid.ToString();
            cmd.Parameters.Add("pn_crane_movement_id_no", OracleDbType.Number).Value = pn_crane_movement_id_no.ToString();
            cmd.Parameters.Add(new OracleParameter("pv_error", OracleDbType.VarChar));
            cmd.Parameters["pv_error"].Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            trans.Commit();
        }

        catch
        {
            pv_error = cmd.Parameters["pv_error"].Value.ToString();
            MessageBox.Show("" + pv_error, "Error");

            try
            {
                trans.Rollback();
            }
            catch (OracleException ex)
            {
                MessageBox.Show("Rollback failed" + ex, "Exception Error");
            }
        }
    }

But because its not an exception error that I'm expecting it doesn't go through the try catch statement. I want it to rollback when my pv_error variable is populated. That is why I included a if statement in the first example.

Also I don't have any other transaction before this one..

what if you put conn.AutoCommit = false; before opening the transaction? — Sebas, Apr 11 '13 at 12:51
Try switching to use a [TransactionScope](http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx) instead. Like [this](http://stackoverflow.com/questions/15268430/how-to-implement-transaction/15268649#15268649) — Jens Kloster, Apr 11 '13 at 12:53

score 0 · Answer 1 · answered Apr 11 '13 at 13:30

0

There is a suggestion here that you may see this error if you already have an open transaction. They advise committing or rolling back anything that's open. Unlike in SQL Server, I have been able to issue prophylactic "COMMIT" statements in Oracle without even knowing whether I have anything open.

The only difference I see between your code and the sample code here is that they tie the command object to the connection before assigning the transaction to it:

OracleCommand command = connection.CreateCommand();
command.Transaction = transaction;

You might want to try that variation. You're not setting an isolation level, so the issue with oracle oci.dll below version 10.2 presumably doesn't apply here.

answered Apr 11 '13 at 13:30

criticalfix

2,870
1
19
32

I have also seen your examples. I have edited my question. In there you will see I have tried it like the example you gave. I also explain why it doesn't work for me. – Werner van den Heever Apr 12 '13 at 12:49
Sorry @Werner, I'm not sure what to try next. Did you try putting the "cmd.Transaction = trans;" before the BeginTransaction? Also, why not continue omitting the isolation level? Do you know the version number of your oci.dll? ... If you do find a solution, it would be great if you could post it for everyone's information. – criticalfix Apr 15 '13 at 12:41

score 0 · Answer 2 · answered Apr 15 '13 at 12:35

According to the MSDN docs, your second version correctly uses the transaction. So if you combine that with your if statement, you should be fine:

  private void stk_crane_start_movement()
    {
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        OracleTransaction trans;
        trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.Transaction = trans;
        try
        {
            cmd.CommandText = "dc.stk_crane_start_movement";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("pn_crane_opr_id_no", OracleDbType.Number).Value = empid.ToString();
            cmd.Parameters.Add("pn_crane_movement_id_no", OracleDbType.Number).Value = pn_crane_movement_id_no.ToString();
            cmd.Parameters.Add(new OracleParameter("pv_error", OracleDbType.VarChar));
            cmd.Parameters["pv_error"].Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            pv_error = cmd.Parameters["pv_error"].Value.ToString();                
            if (pv_error.ToString() == "")
            {
               trans.Commit();
               trans.Dispose();
               conn.Close();
               cmd.Dispose();
            }
            else
            {
              trans.Rollback();
              MessageBox.Show("" + pv_error, "Error");
            }

SET TRANSACTION error , C# , Oracle

2 Answers2