OBIEE 10G/11G - Oracle Call Interface (OCI) configuration

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics)

1 - About

OCI stands for Oracle Call Interface (OCI).

It's an Call Level Interface API (such as ODBC and JDBC but specific to Oracle) that lets you develop application and access an Oracle Database.

Advertising

3 - Prerequisites

3.1 - 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.

3.2 - 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

4 - 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.

4.1 - 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.

Advertising

4.2 - Easy Connect Method

A SQL Connect URL such as

//database_host[:port]/service_name

4.3 - Connect Descriptor

You can also enter a connect descriptor.

( 
  DESCRIPTION= 
  (
    ADDRESS=(PROTOCOL=tcp)(HOST=oracle11gr2.gerardnico.local)(PORT=1521)
  ) 
  (
    CONNECT_DATA=(SERVICE_NAME=ORCL)
  )
)

5 - Support

5.1 - 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
Advertising

5.2 - 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.

6 - Documentation / Reference

dat/obiee/oci.txt · Last modified: 2018/03/26 11:11 by 141.101.69.67