I have this problem in my ASP.NET application where I'm seeing some of my Oracle queries fired off to the server then not returning. Ever. It happens in several places in my app and I can't explain it. Here's one specific scenario where I'm seeing this behavior:
During application start-up I am pre-fetching data asynchronously into the application state (the choice was made to use app state instead of cache b/c the data never changes during the lifetime of the app).
Action<string, object> AddApplicationState = (string name, object data) =>
{
Application.Lock();
Application.Add(name, data);
Application.UnLock();
};
Func<DataTable> GetFullNames = () => Database.GetAllNames();
Func<DataTable> GetProvinceNames = () => Database.GetProvinceNames();
Func<DataTable> GetTribeNames = () => Database.GetTribeNames();
GetFullNames.BeginInvoke(result => AddApplicationState("AllNames", GetFullNames.EndInvoke(result)), null);
GetProvinceNames.BeginInvoke(result => AddApplicationState("ProvinceNames", GetProvinceNames.EndInvoke(result)), null);
GetTribeNames.BeginInvoke(result => AddApplicationState("TribeNames", GetTribeNames.EndInvoke(result)), null);
The second two return just fine, but the first either never returns or returns after about 10 minutes. After firing up Oracle SQL Developer I go to the 'monitor sessions' tool and can see a single session for the query. It looks like it has completed, b/c the wait time is (null) and the session is inactive. Here's the ADO.NET code used to query the database:
public static DataTable GetAllNames()
{
using (OracleConnection oraconn = GetConnection())
{
using (OracleCommand oracmd = GetCommand(oraconn))
{
var sql = new StringBuilder();
sql.AppendLine("SELECT NAME_ID, NATIVE_NAME, NVL(FREQUENCY,0) \"FREQUENCY\", CULTURE_ID,");
sql.AppendLine("ENGLISH_NAME, REGEXP_REPLACE(ENGLISH_NAME, '[^A-Za-z]', null) \"ENGLISH_NAME_STRIPPED\"");
sql.AppendLine("FROM NAMES");
oracmd.CommandText = sql.ToString();
var orada = new OracleDataAdapter(oracmd);
var dtAllNames = new DataTable();
orada.Fill(dtAllNames);
return dtAllNames;
}
}
}
public static DataTable GetTribeNames()
{
using (OracleConnection oraconn = GetConnection())
{
using (OracleCommand oracmd = GetCommand(oraconn))
{
var sql = new StringBuilder();
sql.AppendLine("SELECT DISTINCT NAME_ID, English_Name \"TRIBE_NAME_ENGLISH\",");
sql.AppendLine("REGEXP_REPLACE(English_Name, '[^A-Za-z]',null) \"TRIBE_ENGLISH_NAME_STRIPPED\",");
sql.AppendLine("NATIVE_NAME \"TRIBE_NATIVE_NAME\"");
sql.AppendLine("FROM NAMES");
sql.AppendLine("WHERE NAME_ID IN ");
sql.AppendLine("(SELECT NAME_ID_TRIBE FROM TRIBES UNION SELECT NAME_ID_FAMILY FROM TRIBES)");
sql.AppendLine("ORDER BY English_Name");
oracmd.CommandText = sql.ToString();
var orada = new OracleDataAdapter(oracmd);
var dt = new DataTable();
orada.Fill(dt);
return dt;
}
}
}
public static DataTable GetProvinceNames()
{
using (OracleConnection oraconn = GetConnection())
{
using (OracleCommand oracmd = GetCommand(oraconn))
{
oracmd.CommandText = "SELECT DISTINCT PROVINCE_ID, PROVINCE_NAME_NATIVE, PROVINCE_NAME_ENGLISH FROM PROVINCES";
var orada = new OracleDataAdapter(oracmd);
var dtRC = new DataTable();
orada.Fill(dtRC);
return dtRC;
}
}
}
As you can see, the ADO.NET code is pretty standard (and boring!) stuff. When run in SQL Developer, the queries return less than a second. The first query returns x rows, the second x rows, and the third x rows. But this problem of queries being fired off then never returning happens often and I can't seem to track down the issue. Anyone have any thoughts?
And finally, since I realize it could be something completely unrelated to code, I am running the app locally (from Visual Studio) on a Windows XP SP3 machine and connecting via VPN to a remote Oracle 10g Enterprise instance running on Windows 2003 Server. Locally, I have installed Oracle Data Access Components v11.1.0.6.20.
Thanks!