Connecting to Oracle Database using JDBC OCI driver

This article discusses how to establish connections to an Oracle database using JDBC OCI (Type II). Please take into consideration that the proposed way uses DBeaver's Generic driver. It means that you cannot get Oracle-specific functionality this way.

JDBC OCI connections require Oracle Instant Client on the local machine. Please pay attention to the Instant Client and the JDBC driver versions, as they must be identical. DBeaver uses the 12.2.0.1 version by default at the moment, so we recommend using the 12.2.0.1 version of the Instant Client.

Extract or mount the Instant Client package as per your operating system and place the files in a selected folder. We will refer to this folder as ORA_HOME for the rest of the article. Append ORA_HOME to the PATH variable and restart DBeaver before proceeding.

1. Creating tnsnames.ora file

   • If you do not have a tnsnames.ora file, create one by following these steps:
     • Open a text editor.
     • Add your Oracle database connection details, which typically include the network alias, a description of the connection, and address parameters such as hostname and port number. For example:

       MYDB =
         (DESCRIPTION =
           (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.example.com)(PORT = 1521))
           (CONNECT_DATA =
             (SERVER = DEDICATED)
             (SERVICE_NAME = mydbservice.example.com)
           )
         )
       

     • Save the file as tnsnames.ora.

2. Place the newly created or existing tnsnames.ora file in the ORA_HOME/network/admin directory.

3. Configuring driver in DBeaver

   • In DBeaver, go to Window -> Driver Manager -> New to open the Create new driver dialog.
   • In the Settings tab:
     • Assign a Driver name as preferred.
     • Set Class Name to oracle.jdbc.OracleDriver.
     • Use jdbc:oracle:oci:@tnsAlias as the URL Template, where tnsAlias is an alias from your tnsnames.ora file.
     • Ensure the Driver Type is Generic.

Tip: While selecting the Generic driver type is recommended for broader compatibility, it's not mandatory. You can choose the Oracle driver type to access Oracle-specific features. However, be aware that using a non-generic driver might lead to unexpected errors with some JDBC functionalities.

4. Adding maven artifacts in DBeaver

   • Under the Libraries tab, click Add Artifact.
   • Insert the below XML in the text field.

   `XML

    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>12.2.0.1</version>
    </dependency>
    <dependency>
        <groupId>com.oracle.database.nls</groupId>
        <artifactId>orai18n</artifactId>
        <version>12.2.0.1</version>
    </dependency>
    <dependency>
        <groupId>com.oracle.database.xml</groupId>
        <artifactId>xdb6</artifactId>
        <version>12.2.0.1</version>
    </dependency>
    <dependency>
        <groupId>com.oracle.database.xml</groupId>
        <artifactId>xmlparserv2</artifactId>
        <version>12.2.0.1</version>
    </dependency>
   

   `

   Note: Replace the versions of the artifacts if you use a different version of the Instant Client.

5. Configuring driver properties

   • Go to the Driver properties tab.
   • Right-click and choose Add new property.
   • Set the property name to protocol (without quotes).
   • Set the Value to oci (without quotes).

6. Once you have configured the properties, close the Driver Manager.

7. Create a new connection in DBeaver using the driver you have just configured.