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,

1. 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).

2. Click Source Properties.

   
   
   
   
   

3. Click SQL Properties.

4. From Properties, select Oracle Call Interface (OCI).

5. In the Server field, enter the Oracle Database connection string, in the format <host>:<port>/<service_name>.

6. In the Query field, enter a SQL query that accesses the data you need from Oracle Database.

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

   
   
   
   
   

8. 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,

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

2. 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).

3. Click Source Properties.

   
   
   
   
   

4. Click SQL Properties.

5. From Properties, select Oracle Call Interface (OCI).

6. In the Server field, enter the net_service_name used in the tnsnames.ora configuration file. Based on the example given in step 1, enter OCISQL.

7. In the Query field, enter a SQL query that accesses the data you need from Oracle Database.

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

   
   
   
   
   

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

1. In <Oracle Home>/network/admin, locate the configuration file.

2. Open the file and add SQLNET.AUTHENTICATION_SERVICES=(NONE)

3. Restart the Essbase application.

Note:

Refer to usage notes in the SQLNET.AUTHENTICATION_SERVICES documentation for Oracle Database 19c.