Hi,
My code (C#) using OracleTransaction is roughly as follows (pseudocode): 
 

main()
{
 connectionString = database
 OracleConnection connection = new OracleConnection(connectionString);
 connection.open();
 OracleCommand cmd = connection.CreateCommand;
 OracleTransaction transaction;
 transaction = connection.BeginTransaction
 try
 {
  cmd.CommandText = INSERT INTO TableA...
  cmd.ExecuteNonQuery
  OracleReader reader = SELECT a, b, c FROM...
  // could be many hundred of rows
  while(reader.Read())
  {
   insertString.append("INSERT INTO TableB VALUES (");
   insertString.append("'" + a + "', '" + b + "', ");
   int i1 = method1(connectionString)
   insertString.append(i1 + ", ");
   int i2 = method2(connectionString);
   insertString.append(i2 + ")");
   cmd.CommandText = insertString.ToString();
   cmd.ExecuteNonQuery;
  }
  transaction.Commit();
 }
 catch
 {
  transaction.Rollback();
 }
 finally
 {
  connection.Close();
 }
}
 
method1(string connectionString)
{
 OracleConnection connection = new OracleConnection(connectionString);
 connection.open();
 try
 {
  ...
  return (int)(OracleNumber)cmd.ExecuteOracleScalar();
 }
 catch
 {
  return integer;
 }
 finally
 {
  connection.close();
 }
}
 
method2(string connectionString)
{
 OracleConnection connection = new OracleConnection(connectionString);
 connection.open();
 try
 {
  ...
  return (int)(OracleNumber)cmd.ExecuteOracleScalar();
 }
 catch
 {
  return integer;
 }
 finally
 {
  connection.close();
 }
}

 
I am using same database connection all the time. My feeling is that I do something not correct/not effective when I open and close the connection all the time when calling sub methods (method1 and method2). Is it really needed because I have had problems with "~ to many connections open... (pool problem... maximum utilized...)"? My feeling is also that this slows down the inserts into TableB, pls advice how to accomplish most correct/appropriate opening/closening of database connection? Any other comments of my code for inserts and ideas of improvements are highly appreciated.
 
Thanks in advance!