Product:
Cognos BI 10.1.1 64 bit version
AIX
Oracle 11.2.0
Java version 1.5

Symptom:
When testing the connection to a oracle data source, the Compatible Query Mode is successful, but you get a error message for Dynamic Query Mode (JDBC).

Error message:
XQE-GEN-0002 An unexpected exception occurred: ocijdbc11 (A file or directory in the path name does not exist.)

Background:
You have installed 32 Bit Oracle client on your Cognos Aix server.
You have set the LIBPATH to /IBM/cognos/oracle/product/11.2.0/client32/lib
You have added oracle path to the PATH variable.
You have copied the ojdbc5.jar file to the /IBM/cognos/c10_64/webapps/p2pd/WEB-INF/lib folder.

Under Dynamic/JDBC connection setting, you can
choose “Service ID”, “TNS Names Alias” and ‘Oracle Net Descriptor”.
This error only occurs on “TNS Names Alias” (OCI driver type only), and ‘Oracle Net
Descriptor” (With OCI driver type).
“Service ID” option and Oracle Net Descriptor (Thin driver type) don’t have this issue.

Solution:
Cognos BI 10.1.1 needs to call 64 Bit Oracle libraries when you use a JAVA OCI driver.
But Compatible Query Mode only supports 32 Bit Oracle client (SQLNET).

Recommendation is to use SERVICE ID to access the Oracle database with JDBC.
Enter the server name to the oracle server using FQDN name.
Enter the port number e.g. 1521.
Enter the oracle service ID, that is the database name.

In previous OCI configuration you set the user name to the schema name
and also enter the password for the user name.

Then test the connection again.

Otherwise you need to install both 32bit and 64bit oracle client and update the LIBPATH accordingly, to maybe get it to work.