2 Replies Latest reply: Nov 13, 2012 9:31 PM by 77774 RSS

    OCI example 10-1 (Continuous Query Notification) callback not invoked

    77774
      I'm trying to get the OCI Continuous Query Notification example (10-1 demoquery.c) from here working on XE 11.2.0.2.0 on Ubunutu 12.04:
      http://docs.oracle.com/cd/E16338_01/appdev.112/e10646/oci10new.htm

      I've had to change the program to give the connection string like so:
      char dsn[] = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine)(PORT=1521)))(CONNECT_DATA=(SID=XE)))";
      ....
      checker(errhp,OCIServerAttach(srvhp, errhp, (text *) &dsn, (sb4) strlen(dsn), (ub4) OCI_DEFAULT));

      (and grant change notification to the HR user). I've compiled like so:
      gcc -Wall -I/usr/include/oracle/11.2/client -c demoquery.c -o demoquery.o
      gcc -lclntsh -Wall -L/usr/lib/oracle/11.2/client/lib demoquery.o -o demoquery

      When I run the example update statements from the top of the source file, the callback function is not invoked. The program output is this:

      Initializing OCI Process
      Registering query : select last_name, employees.department_id, department_name from employees,departments where employee_id = 200 and employees.department_id = departments.department_id
      Query Id 21
      Waiting for Notifications

      ... and it waits indefinitely (until killed).

      The program clearly registers on the server:

      select username, table_name, callback from dba_CHANGE_NOTIFICATION_REGS;

      HR HR.EMPLOYEES net8://(ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=41756))?PR=0
      HR HR.DEPARTMENTS net8://(ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=41756))?PR=0

      I've used tcpdump to watch the network traffic, and when the update is committed there is communication from the server to the client port 41756, and the last packet from the server [PSH, ACK] contains the table name and what looks like the rowid.
      From tcpdump -A, trimmed to the last part of the packet:
      .....HR.DEPARTMENTS..Q.........AAAFEcAAFAAAA1tAAA

      So it looks as though the fault lies with the OCI library, or the way the client program registers for callbacks.

      FWIW, I've successfully tested an in-database PLSQL callback.

      Thanks,
      Alistair