object reference error when define oracle transaction

Question

User-1141190189 posted

I am facing this error when define the oracle transaction "Object reference not set to an instance of an object". when trying to define the transaction OracleTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)

public int RunOracleTransaction(Student s, Marks[] m, Course[] c) {
 //TODO: validate s, m, c

using (OracleConnection connection = new OracleConnection(connectionString)) {
connection.Open();

using (OracleCommand command = connection.CreateCommand()) {
  using (OracleTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)) {
    command.Transaction = transaction;

    try {
      // Insert the student
      //TODO: put actual query here 
      command.CommandText = 
        @"insert into Students(name)
               values (:prm_Name)
            returning id into :prm_id"; // <- we want inserted student's id

      //TODO: check actual RDBMS types 
      command.Parameters.Add(":prm_Name", OracleType.VarChar).Value = s.Name;
      command.Parameters.Add(":prm_Id", OracleType.VarChar).Direction = ParameterDirection.Output;

      command.ExecuteNonQuery(); 

      string studentId = Convert.ToString(comm.Parameters[":prm_Id"].Value);

      // Insert his/her marks
      command.Parameters.Clear(); // <- forget all prior parameters

      //TODO: put actual query here 
      command.CommandText = 
        @"insert into StudentsMarks(student_Id, mark)
               values (:prm_Student_Id, :prm_Mark)";

      //TODO: check actual RDBMS types 
      command.Parameters.Add(":prm_Student_Id", OracleType.VarChar).Value = studentId;
      command.Parameters.Add(":prm_Mark", OracleType.Int32);

      // insert each mark (in a loop)
      foreach (var mark in m) {
        command.Parameters[":prm_Mark"].Value = m.Mark;
        command.ExecuteNonQuery();  
      }

      // Finally, commit all the inserts
      transaction.Commit();
    }
    catch (DataException e) {
      transaction.Rollback();

      Console.WriteLine(e.ToString());
      Console.WriteLine("Neither record was written to database.");
    }
  }
} 
}

  //TODO: your method returns integer value, please return it  
}

Answer 1

User2103319870 posted

Sanfoor

when trying to define the transaction OracleTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)

Most likely the connection object might not have been created or doesn't has an active connection. As a troubleshooting step you might consider wrapping your entire code inside try catch block and see if you are getting any exceptions.

public int RunOracleTransaction(Student s, Marks[] m, Course[] c)
        {
            //TODO: validate s, m, c
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    connection.Open();

                    using (OracleCommand command = connection.CreateCommand())
                    {
                        using (OracleTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
                        {
                            command.Transaction = transaction;


                            // Insert the student
                            //TODO: put actual query here 
                            command.CommandText =
                              @"insert into Students(name)
               values (:prm_Name)
            returning id into :prm_id"; // <- we want inserted student's id

                            //TODO: check actual RDBMS types 
                            command.Parameters.Add(":prm_Name", OracleType.VarChar).Value = s.Name;
                            command.Parameters.Add(":prm_Id", OracleType.VarChar).Direction = ParameterDirection.Output;

                            command.ExecuteNonQuery();

                            string studentId = Convert.ToString(comm.Parameters[":prm_Id"].Value);

                            // Insert his/her marks
                            command.Parameters.Clear(); // <- forget all prior parameters

                            //TODO: put actual query here 
                            command.CommandText =
                              @"insert into StudentsMarks(student_Id, mark)
               values (:prm_Student_Id, :prm_Mark)";

                            //TODO: check actual RDBMS types 
                            command.Parameters.Add(":prm_Student_Id", OracleType.VarChar).Value = studentId;
                            command.Parameters.Add(":prm_Mark", OracleType.Int32);

                            // insert each mark (in a loop)
                            foreach (var mark in m)
                            {
                                command.Parameters[":prm_Mark"].Value = m.Mark;
                                command.ExecuteNonQuery();
                            }

                            // Finally, commit all the inserts
                            transaction.Commit();

                        }
                    }
                }
            }
            catch (DataException e)
            {
                //transaction.Rollback();

                Console.WriteLine(e.ToString());
                Console.WriteLine("Neither record was written to database.");
            }

            //TODO: your method returns integer value, please return it  
        }

Answer 2

User269602965 posted

Fix punctuation problems first

1. There is no need for AT SYMBOL before insert command

2. There is no need for COLON SYMBOL as part of the PARAMETER NAME, but keep colon in the INSERT STATEMENT so oracle knows it is a bind variable.

Example of command and bind variables.

I have also found it easier to enclose my SQL in an XML tag then I do not have to worry about escaping punctuation or managing CRLF messing with string formation.

I do not have the TRANSACTION wrapper here.

Imports System.Xml.Linq.XElement
Try
  Dim SQL = <SQL>
              UPDATE {YourSchemaName}.HELP_TICKET
              SET TICKET_TIMESTAMP = :TICKET_TIMESTAMP
              WHERE TICKET_SEQ = :TICKET_SEQ
            </SQL>
  Using conn As New OracleConnection(connectionString)
    Using cmd As New OracleCommand(SQL.Value, conn)
      cmd.Parameters.Clear()
      cmd.Parameters.Add("TICKET_TIMESTAMP", OracleDbType.Date, DateTimeStamp, ParameterDirection.Input)
      cmd.Parameters.Add("TICKET_SEQ", OracleDbType.Decimal, decTicketSeq, ParameterDirection.Input)
      conn.Open()
      cmd.ExecuteNonQuery()
    End Using
  End Using
Catch ex As Exception
End Try