KBEC-00331 - Connect to Oracle database with JDBC OCI

Summary

ElectricFlow connects to database with JDBC Thin driver by default. You can also use JDBC OCI to connect to the database if the database server is Oracle.

Solution

To connect Oracle database with JDBC OCI, update the settings in database.properties. Specifically setting the value in COMMANDER_CUSTOM_DB_URL.

Here is an example:

1. Install Oracle Instant Client from http://www.oracle.com/technetwork/topics/winx64soft-089540.html

2. decompress Instant Client, for example to c:\instantclient

3. Add "set.PATH=c:/instantclient%WRAPPER_PATH_SEPARATOR%%PATH%" to ElectricFlow config file wrapper.conf

4. In ElectricFlow config file database.properties, add/modify COMMANDER_CUSTOM_DB_URL to value "jdbc\:oracle\:oci\:@192.168.116.128\:1521\:xe"

5. Restart ElectricFlow server and we can confirm that it connects to Oracle database with JDBC OCI successfully.

2015-12-07T08:31:24.568 | INFO  | bootstrap                      |          |                                      |                                                                             | ServerStatus                   | JDBC user: COMMANDER5
2015-12-07T08:31:25.016 | INFO  | bootstrap                      |          |                                      |                                                                             | ServerStatus                   | Database server product:version [Oracle:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production], jdbc driver product:version [Oracle JDBC driver:12.1.0.2.0]
2015-12-07T08:31:25.016 | INFO  | bootstrap                      |          |                                      |                                                                             | ServerStatus                   | Connected to the database

You can also specify the Oracle database server host and IP in file tnsnames.ora. For example, following the above example:

6. Stop ElectricFlow server

7. Add system variable TNS_ADMIN and set its value to "c:\instantclient"

8. Create tnsnames.ora in c:\instantclient and update it so it contains the following contnet:

dg = 
    (DESCRIPTION = 
        (ADDRESS_LIST = 
            (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.116.128) (PORT = 1521))
        )
        (CONNECT_DATA = 
            (SERVICE_NAME = xe)
        )
    )

9. In ElectricFlow config file database.properties, modify COMMANDER_CUSTOM_DB_URL to value "jdbc\:oracle\:oci\:@dg"

10. Start ElectricFlow server and we can confirm that it connects to Oracle database with JDBC OCI successfully.

2015-12-07T10:13:18.388 | INFO  | bootstrap                      |          |                                      |                                                                             | ServerStatus                   | JDBC url: jdbc:oracle:oci:@dg
2015-12-07T10:13:18.389 | INFO  | bootstrap                      |          |                                      |                                                                             | ServerStatus                   | JDBC user: COMMANDER5
2015-12-07T10:13:18.761 | INFO  | bootstrap                      |          |                                      |                                                                             | ServerStatus                   | Database server product:version [Oracle:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production], jdbc driver product:version [Oracle JDBC driver:12.1.0.2.0]
2015-12-07T10:13:18.761 | INFO  | bootstrap                      |          |                                      |                                                                             | ServerStatus                   | Connected to the database

Please find more information in the following pages:

http://www.orafaq.com/wiki/JDBC

http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/toc.htm

Applies to

• Product versions: all
• OS versions: all