About
This page shows you how to install an OCI driver with OBIEE.
Articles Related
Prerequisites
10g
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.
11g
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
Connect identifier
To configure OCI, you have to provide in the connection pool of the physical layer three parameters:
- the call interface. You must choose OCI.
- The data source name
- The user name and password
In the data source name, you have to enter a connect identifier that can be one of the following naming methods.
Net Service Name
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.
Easy Connect Method
A SQL Connect URL such as
//database_host[:port]/service_name
Connect Descriptor
You can also enter a connect descriptor.
(
DESCRIPTION=
(
ADDRESS=(PROTOCOL=tcp)(HOST=oracle11gr2.gerardnico.local)(PORT=1521)
)
(
CONNECT_DATA=(SERVICE_NAME=ORCL)
)
)
Support
ORA-12154: TNS:could not resolve the connect identifier
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:
- the tnsname naming method, verify that you have set the TNS_ADMIN environment:
- for a remote server import: for the user who start OBIEE. Alternatively, you can create a TNS file in the directory ORACLE_BI_HOME\network\admin
- for a local import: in the bi-init file.
- the easy connect method, verify your syntax
ODBC limitations
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.