CODEWITHSUNDEEP
oracle-databasenotificationsodp.net
A.Escalera
A.Escalera

Reputation: 21

ODP.NET notifications are not received in the client side

I have the following deployment:

A) Server Machine:

B) Client Machine:

In the Client Machine I have a very simple application that performs the following steps:

  1. Creates an OracleConnection
  2. Creates an OracleCommand (SELECT FIELD FROM TABLE)
  3. Creates an OracleDependency based on the previous command

Once these steps are performed I am able to see in the DBA_CHANGE_NOTIFICATION_REGS table that a new record has been inserted. The information in this record perfectly matches with my client settings (user connected, IP address, IP port).

Then, I modify the DB being sure that the result of the query to be notified changes after this modification. My application should be notified but it is not.

I have checked (using Wireshark) that, after the modification, some traffic is sent form the server to the client through the port specified in the DBA_CHANGE_NOTIFICATION_REGS but the client application it is not notified.

So, it seems that the notification is created correctly, the notification from the server to the client computer is also sent, but the notification does not reach the application.

What else do I need in the client side in order to receive the notification?

Thanks in advance....

Here the code I am using:

private void buttonConnectOracle_Click(object sender, EventArgs e)
{
    String constr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.164.10.202)(PORT=30005))" + 
                    "(CONNECT_DATA=(SID=MASI)));User Id=dbuser;Password=dbuserpasswd;";

    try
    {
        OracleCon = new OracleConnection(constr);
        OracleCon.Open();
        buttonConectarOracle.Enabled = false;
        RegisterOracleCommandDependency();
    }
    catch (Exception E)
    {
        MessageBox.Show(String.Format("ERROR connecting with Oracle BD: {0}{0}Connection string: {1}{0}{0}Error:{0}{2}", "\n\r", constr, E.Message));
    }
}

private void RegisterOracleCommandDependency()
{
    String sql = "SELECT * FROM SCEHMA.REF";
    OracleCommand command = new OracleCommand(sql, OracleCon);

    OracleDep = new OracleDependency(command);
    command.Notification.IsNotifiedOnce = false;

    command.ExecuteNonQuery();

    OracleDep.OnChange += new Oracle.DataAccess.Client.OnChangeEventHandler(OnMyNotificationOracle);
}

public void OnMyNotificationOracle(object src, OracleNotificationEventArgs args) 
{
    String logStr = "Notification received at " + DateTime.Now.ToString();
    //TODO: Add logStr to log
}

Upvotes: 1

Views: 1295

Answers (4)

Charnichart
Charnichart

Reputation: 41

Maybe this could help

using (OracleConnection OraConn = new OracleConnection(conString))
{
    using (OracleCommand OraComm = OraConn.CreateCommand())
    {
        try
        {
            OracleDependency OraDep = new OracleDependency(OraComm);
            OraComm.AddRowid = true;
            OraComm.CommandText = crawlORACLEServer;
            OraComm.CommandType = System.Data.CommandType.Text;
            OraComm.Notification.IsNotifiedOnce = false;
            OraDep.OnChange += OraDep_OnChange;

            if (OraConn.State == System.Data.ConnectionState.Closed) OraConn.Open();

            dynamic f = await OraComm.ExecuteNonQueryAsync();

            bRet = true;
        }
        catch (OracleNullValueException eN)
        {
            message = eN.Message;
        }
        catch (OracleTruncateException OT)
        {
            message = OT.Message;
        }
        catch (OracleTypeException eT)
        {
            message = eT.Message;
        }
        catch (OracleException ex)
        {
            message = ex.Message;
        }
        catch (Exception e)
        {
            message = e.Message;
        }
        finally
        {
            OraComm.Dispose();
            OraConn.Dispose();
        }
    }
}

Upvotes: 0

Ale
Ale

Reputation: 85

Just to add to the incompatible versions listing above; I am having the same issues with Oracle 11g Release 11.2.0.3.0 (64bit), and using ODP.NET (Oracle.ManagedDataAccess) version 12.1.24160719.

Get an entry in the USER_CHANGE_NOTIFICATION_REGS table (Host IP does not match my client IP however, hmmm...) This entry is removed after about 30 secs. OnChangeEventHandler never triggered in the .Net client upon inserts (using object-based notification).

Did both:

GRANT CHANGE NOTIFICATION TO [USER]
GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO [USER]

Tried the other ODP.NET client versions available from nuget now, down to 10.1.21. None worked. Wireshark detects no traffic whatsoever on port 1005 (used by OracleDependency) when executing inserts. Sigh.

Update: It turns out that callback failed due to IP redirects that had been setup at the server, so no blame to Oracle in this case... A tip is always to verify the IP adress specified in USER_CHANGE_NOTIFICATION_REGS.

Upvotes: 1

A.Escalera
A.Escalera

Reputation: 21

First of all, thanks to @b_levitt, @yopez83 and @Christian Shay for their contributions.

The issue has been solved: there is an incompatibility problem between some Oracle Server versions and the corresponding Oracle Client versions.

As a summary, notifications do not work when Server is 11.2.0.2.0 and Client is 11.2.0.1.2.

This query gives information about the Oracle server version:

select * from v$version;

In my case, the output was the following:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Hope this help....

Upvotes: 1

dotnetspark
dotnetspark

Reputation: 581

Check this documentation. I think it will help you with the notification.

Also, see this, if the previous link didn't help.

Upvotes: 0

Related Questions