OracleCommand execution blocks if has OracleDepend

Question

I have the following code:

OracleConnection conn = new OracleConnection(connString);
OracleCommand command = new OracleCommand("select * from testtable", conn);
conn.Open();
OracleDependency.Port = 2010;
OracleDependency dependency = new OracleDependency(command);
command.AddRowid = true;
command.Notification.IsNotifiedOnce = false;

dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

command.CommandTimeout = 1000;
DataTable t = new DataTable();
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(t);
conn.Close();

This is a very straightforward code that uses Oracle Notification Service to receive notifications about particular table changes.

My problem is that when I call adapter.Fill(t); the execution simply blocks. The command executes in an instance if there is no dependency attached to it so it's not the database or the data. I can see the call back registering with the database by querying the table user_change_notification_regs and have also opened the port specified (2010):

net8://(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST='myIp')(PORT=2010)))?PR=0

I am at wits end and rand out of things to try.

Answer 1

I have seen an exception raised in a similar situation when I've tried to set the port number to a port already used on my machine. As soon as I commented out setting the port number it ran fine, so perhaps you could try that? And check "netstat -na" for used ports.

The exception I saw was:

Oracle.DataAccess.Client.OracleException: ORA-24912: Listener thread failed. Listen failed.
at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

The confusing thing (at least for me) was the exception is raised not when the port is set, but later when the first query was executed against it.