title: Truncating an Oracle Temp Table in a Transaction, Truncates *ALL* Temp Tables
body: <p>I have some C# code that creates several Oracle Temporary Tables with the "ON COMMIT DELETE ROWS" option inside of a transaction.</p>
  
  <p>Inside of the transaction, I will insert a bunch of rows in to various temp tables.  In some scenarios I need to Truncate a particular temp table so that I can start fresh with that table, but leave the other temp tables alone.</p>
  
  <p>I found that Oracle must be doing an implicit COMMIT when you perform the Truncate, as not only was the particular temp table being Truncated, but <em>all</em> of my temp tables are being truncated.</p>
  
  <p>Ok, I've read elsewhere that the Truncate command is considered a DDL command and that is why the commit is being processed which results in my "ON COMMIT DELETE ROWS" temp tables being cleared.</p>
  
  <p>If that is true, wouldn't the act of creating a new temp table also be a DDL command and it would also trip the same commit clearing all of the other temp tables?  If so, I haven't seen that behavior.  I have created new temp tables in my code and found that the previously created temp tables still have their rows intact after the new temp table has been created.</p>
  
  <p>Here's some C# code that demonstrates the problem (helper routines not included here):</p>
  
  <pre><code>private void RunTest()
  {
    if (_oc == null)
      _oc = new OracleConnection("data source=myserver;user id=myid;password=mypassword");
  
    _oc.Open();
    _tran = _oc.BeginTransaction();
  
    string tt1 = "DMTEST1";
    AddTempTable(tt1, false);
    int TempTableRowCount0 = GetTempTableRowCount(tt1);
  
    AddRows(tt1, 5);
    int TempTableRowCount10 = GetTempTableRowCount(tt1);
  
    string tt2 = "DMTEST2";
    AddTempTable(tt2, false);
    int TempTableRowCount12 = GetTempTableRowCount(tt1); // This will have the same value as TempTableRowCount10
    AddRows(tt2, 6);
    int TempTableRowCount13 = GetTempTableRowCount(tt2); // This will have the same value as TempTableRowCount10
  
    string tt3 = "DMTEST3";
    AddTempTable(tt3, true); // The TRUE argument which does a TRUNCATE against the DMTEST3 table is the problem
    int TempTableRowCount14 = GetTempTableRowCount(tt1); // This will return 0, it should be = TempTableRowCount10
    int TempTableRowCount15 = GetTempTableRowCount(tt2); // This will return 0, it should be = TempTableRowCount13
  
    _tran.Commit();
    _tran = null;
    int TempTableRowCount20 = GetTempTableRowCount(tt1); // This should be 0 because the transaction was committed 
    int TempTableRowCount21 = GetTempTableRowCount(tt2); // and the temp tables are defined as "ON COMMIT DELETE ROWS"
  }
  
  private void AddTempTable(string TableName, bool Truncate)
  {
    IDbCommand ocmd = new OracleCommand();
    ocmd.Connection = _oc;
    if (!TableExists(TableName))
    {
      ocmd.CommandText = string.Format("CREATE GLOBAL TEMPORARY TABLE {0} ({1}) ON COMMIT DELETE ROWS", TableName, "FIELD1 Float");
      int rc = ocmd.ExecuteNonQuery();
    }
    if (Truncate)
    {
      ocmd.CommandText = "TRUNCATE TABLE " + TableName;
      int rc = ocmd.ExecuteNonQuery();
    }
  }
  </code></pre>