Access Oracle Database Using Oracle Call Interface
If you use applications developed with Oracle Call Interface (OCI), you can connect Essbase load rules to Oracle Database using either an OCI connection string, or a net service name defined in a tnsnames.ora
configuration file on the Essbase Server.
Rule Properties with Oracle Call Interface Connection String
To connect an Essbase load rule to Oracle Database using an OCI connection string,
-
In the Essbase web interface, open an existing rule or create a new one (see Access and Edit Rules, Create New Dimension Build Rule, or Create New Data Load Rule).
-
Click Source Properties.
-
Click SQL Properties.
-
From Properties, select Oracle Call Interface (OCI).
-
In the Server field, enter the Oracle Database connection string, in the format
<host>:<port>/<service_name>
. -
In the Query field, enter a SQL query that accesses the data you need from Oracle Database.
-
In the User Name and Password fields, provide the credentials required to access the Oracle Database schema. This information is used for previewing data, but will not be saved in the rule.
-
Click Preview to ensure that the rule can connect and retrieve data, then save and close the rule.
Load Rule with Oracle Call Interface Predefined in Configuration File
To connect an Essbase load rule to Oracle Database using a net_service_name defined in a configuration file on the Essbase Server,
-
On the Essbase Server, create a configuration file (for example, named
tnsnames.ora
) in<Oracle Home>/network/admin
.If the configuration file already exists, ensure it has a valid entry for connecting to Oracle Database using Oracle Call Interface (OCI). Otherwise, configure it now. In the following example,
OCISQL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost.example.com)(PORT = 1521)) ) (CONNECT_DATA = (service_name = orcl.example.com) (SERVER = DEDICATED) ) )
- The net_service_name is
OCISQL
. Take note of this name, which you will need for the next steps. - The host is
oraclehost.example.com
. - The port is
1521
. - The service name is
orcl.example.com
.
- The net_service_name is
-
In the Essbase web interface, open an existing data load or dimension build rule, or create a new one (see Access and Edit Rules, Create New Dimension Build Rule, or Create New Data Load Rule).
-
Click Source Properties.
-
Click SQL Properties.
-
From Properties, select Oracle Call Interface (OCI).
-
In the Server field, enter the net_service_name used in the
tnsnames.ora
configuration file. Based on the example given in step 1, enterOCISQL
. -
In the Query field, enter a SQL query that accesses the data you need from Oracle Database.
-
In the User Name and Password fields, provide the credentials required to access the Oracle Database schema. This information is used for previewing data, but will not be saved in the rule.
-
Click Preview to ensure that the rule can connect and retrieve data, then save and close the rule.
Troubleshooting Connectivity
On Windows, previewing data or running a dimension build or data load with a rule that uses an Oracle Call Interface (OCI) connection string may fail with the following error message:
Attempt to connect to OCI failed. [ORA-12638: Credential retrieval failed].
The cause of this issue is that the version 19c Oracle Database server and the 19c client (in this case the client is where Essbase Server is installed) has no setting, or has the following setting in configured in tnsnames.ora
(or sqlnet.ora
): SQLNET.AUTHENTICATION_SERVICES=(NTS)
The solution is to configure SQLNET.AUTHENTICATION_SERVICES=(NONE) on the Essbase Server machine.
-
In
<Oracle Home>/network/admin
, locate the configuration file. -
Open the file and add
SQLNET.AUTHENTICATION_SERVICES=(NONE)
-
Restart the Essbase application.
Note:
Refer to usage notes in the SQLNET.AUTHENTICATION_SERVICES documentation for Oracle Database 19c.