I'm trying to get the OCI Continuous Query Notification example (10-1 demoquery.c) from here working on XE 126.96.36.199.0 on Ubunutu 12.04:
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=188.8.131.52)(PORT=41756))?PR=0
HR HR.DEPARTMENTS net8://(ADDRESS=(PROTOCOL=tcp)(HOST=184.108.40.206)(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:
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.
It might be worthwhile to set the following event in the database as a workaround:
alter system set events '10867 trace name context forever, level 1';
You can review the following My Oracle Support note for more details if you have a valid support login:
11.2 Client Unable To Receive Notifications From 11.2 Database [ID 1310859.1]