Temporary table not clearing rows across connections

From: <narayan.k.verma_at_gmail.com>
Date: 1 May 2006 18:10:23 -0700
Message-ID: <1146532223.630036.20540@v46g2000cwv.googlegroups.com>

I create the following table:
CREATE GLOBAL TEMPORARY TABLE TT_EmpTable

	(
		EmpId	INT NOT NULL,
		EmpName VARCHAR2(30) NOT NULL)

ON COMMIT PRESERVE ROWS; Then I am executing the following .NET code from a console application:

using System;
using System.Data;
using System.Data.OracleClient;

namespace TempTableTest
{

	class MainClass
	{
		[STAThread]
		static void Main(string[] args)
		{
			OracleConnection connection = new OracleConnection("Data
Source=orcl;User ID=userid;Password=password;");
			OracleCommand command = connection.CreateCommand();
			string query = "begin insert into TT_EmpTable(EmpId, EmpName)

values(1, 'Test Employee'); OPEN :pCUROUT FOR SELECT * from TT_EmpTable;end;";

			command.CommandText = query;
			OracleParameter op = new OracleParameter("pCUROUT",

OracleType.Cursor, 0, ParameterDirection.Output, false, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);

			op.DbType = DbType.Object;
			command.Parameters.Add(op);

			OracleDataAdapter adapter = new OracleDataAdapter(command);
			DataSet ds = new DataSet();
			connection.Open();
			adapter.Fill(ds);
			connection.Close();
			connection.Dispose();

			Console.WriteLine(ds.Tables[0].Rows.Count); //first time it should

show 1 record

                        connection = new OracleConnection("Data Source=orcl;User ID=userid;Password=password;");

			command = connection.CreateCommand();
			query = "begin insert into TT_EmpTable(EmpId, EmpName) values(2,

'Another test employee'); OPEN :pCUROUT FOR SELECT * from TT_EmpTable;end;";

			command.CommandText = query;
			op = new OracleParameter("pCUROUT", OracleType.Cursor, 0,

ParameterDirection.Output, false, 0, 0, String.Empty, DataRowVersion.Default, DBNull.Value);

			op.DbType = DbType.Object;
			command.Parameters.Add(op);

			adapter = new OracleDataAdapter(command);
			ds = new DataSet();
			connection.Open();
			adapter.Fill(ds);
			connection.Close();
			connection.Dispose();

			Console.WriteLine(ds.Tables[0].Rows.Count); //second time it should

also show 1 record

			Console.ReadLine();
		}
	}

}

my problem is that I am seeing two rows the second time around. I was expecting only one row as when I did the first connection.Close that should have closed the session and dropped the row for the next call. But apparently the row added in the first call is also appearing in the second call. This issue is baffling me for a couple of days with no solution.

Any pointers/guidance welcome.

Thanks a lot,
Narayan

P.S. It does not matter if I let DataAdapter handle the opening and closing of connection. Received on Mon May 01 2006 - 20:10:23 CDT