Hi,
I have implemented a small application compiled with VisualStudio 2008 based on the sample source code in "Continuous Query Notification Example" in the 11gR2 documentation "Call Interface Programmer's Guide", chapter 10 "More OCI Advanced Topics" section "Continuous Query Notification". My implementation (of the change notification callback function) writes one line to stdout formatted as "<database name>.<schema name>.<table name>.<row id> <operation codes>\r\n" for each change notification detected.
The same application source code is compiled using thre different projects and called using three different environments for the spplication running with the (1) XE 11gR2 Server OCI, (2) 32-bit 11gR2 Instant Client OCI, and (3) 64bit 11gR2 Instant Client OCI. Each project defines its own paths to headers (such as "oci.h"), libraries (such as "oci.lib"), each environment uses its own path to the dlls (such as "oci.dll") when the application is started.
Here is the problem: Initialization, query registration, and change detection/notification works perfectly when the application is compiled for and running with 64-bit XE 11gR2 Server OCI, but I do not get any change notifications when the application is compiled for and running with 32/64-bit 11gR2 Instant Client OCI (the registered callback function in never called). Compilation of the three projects yields no compiler warnings, the OCI function calls during initialization and query registration do not return any warnings or errors when running the three compiled applications in their respective environments. Startup and shutdown of the applications look exactly the same for the three calling scenarios, however when running with OCI of the 32/64-bit 11gR2 Instant Client nothing happends when making changes to a table in a registered query.
I already have proof that the 64-bit XE 11gR2 Server supports change notifications over OCI through successfully running the first project.
Here are my questions: Does the 64-bit XE 11gR2 Server support change notifications over OCI on the client side when running with OCI of the 32/64-bit 11gR2 Instant Client? If there are restrictions, where are those documented? Has anybody gotten change notifications over OCI to work when running with 32/64-bit 11gR2 Instant Client connected with 64-bit XE 11gR2 Server? Are there any particular environment variables or database permissions required to get this to work?
I have set up a development system with an 64-bit XE 11gR2 Server (setup executable installed under C:\Oracle\oraclexe\app\oracle\product\11.2.0\server), a 32-bit 11gR2 Instant Client (zip archives unpacked under C:\Oracle\instantclient_11_2_x86), and a 64-bit 11gR2 Instant Client (zip archives unpacked under C:\Oracle\instantclient_11_2_x64). The 11gR2 Instant Clients include the unpacked basic and SDK zip archives for their repective architectures (32/64 bit). In addition, I have set up a 32-bit Windows XP and a 64-bit Windows 7 virtual machine with just the matching architecture (32/64 bit) Instant Client to avoid possible interference from the XE 11gR2 Server installation. However, to no effect: regardless of calling the applications on the development system or the virtual machines: running with OCI of the 64-bit XE 11gR2 Server returns change netifications, running with OCI of the 32/64-bit 11gR2 Instant Client does not.
The user/schema I use to test notification of table changes has the DBA role, so all neccessary permissions should be set (including the required SELECT and CHANGE NOTIFICATION permissions).
(1) 64-bit XE 11gR2 Server: c-headers from C:\Oracle\oraclexe\app\oracle\product\11.2.0\server\oci\include, libraries from C:\Oracle\oraclexe\app\oracle\product\11.2.0\server\oci\lib\MSVC, dlls (such as oci.dll) from C:\Oracle\oraclexe\app\oracle\product\11.2.0\server\bin, environment path set to C:\Oracle\oraclexe\app\oracle\product\11.2.0\server\bin, no additional environment variable required when attaching to default server instead to named server (already set in windows registry by server installation), the configuration file C:\Oracle\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora contains
=================================================================
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PortegeR930)(PORT = 1521))
(CONNECT\_DATA =
(SERVER = DEDICATED)
(SERVICE\_NAME = XE)
)
)
EXTPROC\_CONNECTION\_DATA =
(DESCRIPTION =
(ADDRESS\_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT\_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
ORACLR\_CONNECTION\_DATA =
(DESCRIPTION =
(ADDRESS\_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT\_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
=================================================================
(2) 32-bit 11gR2 Instant Client: c-headers from C:\Oracle\instantclient_11_2_x86\sdk\include, libraries from C:\Oracle\instantclient_11_2_x86\sdk\lib\msvc, dlls (such as oci.dll) from C:\Oracle\instantclient_11_2_x86, environment path set to C:\Oracle\instantclient_11_2_x86, environment variable LOCAL=<server> must be set when attaching to default server instead explicitly named server, the configuration file C:\Oracle\instantclient_11_2_x86\network\admin\tnsnames.ora contains
=================================================================
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PortegeR930)(PORT = 1521))
(CONNECT\_DATA =
(SERVICE\_NAME = XE)
)
)
=================================================================
(3) 64-bit 11gR2 Instant Client: c-headers from C:\Oracle\instantclient_11_2_x64\sdk\include, libraries from C:\Oracle\instantclient_11_2_x64\sdk\lib\msvc, dlls (such as oci.dll) from C:\Oracle\instantclient_11_2_x64, environment path set to C:\Oracle\instantclient_11_2_x64, environment variable LOCAL=<server> must be set when attaching to default server instead to explicitly named server, the configuration file C:\Oracle\instantclient_11_2_x64\network\admin\tnsnames.ora contains
=================================================================
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PortegeR930)(PORT = 1521))
(CONNECT\_DATA =
(SERVICE\_NAME = XE)
)
)
=================================================================
I am happy to share the executables or the Visual Studio 2008 projects with source code.
Regards,
Martin.
martin.mueller1@t-online.de