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

Obi Edition

About

This page shows you how to install an OCI driver with OBIEE.

Obiee Connection Pool

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

Obiee Oci

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.

Documentation / Reference





Discover More
Bi Server Architecture With Client
OBIEE - Physical Layer

The physical layer is the layer of the Logical Business Model where you define the physical data model of your data source. It contains information the physical data sources. The most common way to...
Obiee Connection Pool
OBIEE - Connection Pool

This articles regroups all the articles and talk the connection pool features of the BI Server such as: the connection script tab ... see below 10G 11G To automate connection pool changes...
Bi Server Architecture With Client
OBIEE - Data Source (Oracle Analytics Adapter)

Oracle BI Server has an extensible and open connectivity layer with a set of adapters that are responsible for communicating with source data servers. An Oracle Analytics Adapter is a dynamically loaded...
Obiee Product Table Location
OBIEE 10G/11G - Event Pooling table (Event Table) to purge the cache after ETL process

The use of an Oracle BI Server event polling table (event table) is a way to notify the Oracle BI Server that one or more physical tables have been updated and then that the query cache entries are stale....



Share this page:
Follow us:
Task Runner