This page shows you how to install an OCI driver with OBIEE.
To use it you have to install it on the BI Server machine through an Oracle Client installation (for instance an Oracle Database, an Oracle Client or an Oracle Instant).
This installations will provide you the oci.dll file which is the OCI binary. And the directory of this binary must be located in the path environment variable of the user who start the BI Server (for a remote server import) or the Administration tool for a local import.
The OCI dll are shipped with the installation.
cd /u01/app/oracle/product/fmw/Oracle_BI1/lib
ls -l *oci*
-rwxr-x--- 1 oracle oinstall 117051 Oct 14 17:12 libnmeoci.so
-rw-r----- 1 oracle oinstall 0 Apr 28 2011 libnmeoci.so.0
To configure OCI, you have to provide in the connection pool of the physical layer three parameters:
In the data source name, you have to enter a connect identifier that can be one of the following naming methods.
A Net Service Name (TnsName).
This descriptor must be defined in the tnsnames.ora file. OCI localized this file with the help of the TNS_ADMIN environment variable.
In 11g locally, you must set this environment variable in the file bi-init
If the TNS_ADMIN environment variable is not set then the ORACLE_HOME variable must be set, and the configuration files are expected to be in the ORACLE_HOME/network/admin directory. In 11g, it's then Oracle_BI_HOME/network/admin.
A SQL Connect URL such as
//database_host[:port]/service_name
You can also enter a connect descriptor.
(
DESCRIPTION=
(
ADDRESS=(PROTOCOL=tcp)(HOST=oracle11gr2.gerardnico.local)(PORT=1521)
)
(
CONNECT_DATA=(SERVICE_NAME=ORCL)
)
)
Odbc driver returned an error (SQLExecDirectW).
Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001]
Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified at OCI call
OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)
For:
Although ODBC can be used to connect to the database there are some limitations with it.
For example, using % columns in a report, would result in errors like
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000]
[nQSError: 10058] A general error has occurred.
[nQSError: 46036] Internal Assertion: Condition m_CountFields == static_cast<int32>(m_ColumnNameVector.size()),
file .\Src\SQCSCacheStorageListStream.cpp, line 221. (HY000)
Above error is related to cache storage. Disabling the Cache resulted in the error:
[Oracle][ODBC][ORA]ORA-00905 Missing keyword error.
Now, after changing it to OCI, report ran fine.