KNIME is an end to end data analytical tool. With its nodes, all analytical processes of data can be easily built.

Every data project starts with accessing the data. Like any other analytical tools KNIME has several ways of accessing different types of data. Our data may be stored as .csv, .xlsx, .txt etc. or in most of real-world cases the data is stored in a database. Here, our main focus is on accessing to data stored in a database.

At the image shown above, we can see different database management system nodes of KNIME. These connector nodes create a connection to corresponding databases. If we want to access to data stored in database, we must implement these nodes into our workflow. However, there is an important configuration difference between the connectors under the green roof and the connector under the orange roof. The difference is MySQL, PostgreSQL and Microsoft SQL Server have defined driver name, but Oracle not.

We have determined the difference between configuration of Oracle Connector and the others so far.

Now, we can see the configuration windows of KNIME Microsoft SQL Server Connector and Oracle Connector nodes. As we see, there is no defined driver name in Oracle Connector while Microsoft SQL Server has. If we have a look at configuration windows of MySQL Connector and PostgreSQL Connector as well, we will see a defined driver name.

All good, but what is the driver?

Driver or JDBC Driver defines and creates a connection between KNIME and corresponding database. So, if we want to create a connection between KNIME and a database, there must be a defined JDBC Driver.

Why is there no defined JDBC Driver in Oracle Connector Node?

Oracle JDBC Driver is not a part of the KNIME, because there is a license restriction of Oracle. So, if we need to use Oracle Connector in our workflow, Oracle JDBC Driver needs to be registered manually. For more detailed information:

https://kni.me/n/_fvEBQvrof1kSz-X

How to register Oracle JDBC Driver manually?

Firstly, we need to download Oracle JDBC Driver related to our database via https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html. Which database release (11g, 12c, …) is installed in your system, we need to download Oracle JDBC Driver related to your own installed database. Having downloaded the Oracle JDBC Driver, we need to register that driver manually. To do that, please follow the following path:

File -> Preferences -> KNIME -> Databases

Now, click Add to register Oracle JDBC Driver manually.

Fill the ID and Name with Oracle, and select the Database Type with oracle. Having configured these parts, please click Add File to show Oracle JDBC file where located in your system. Then, click Find driver classes and KNIME find our driver class and version. Click OK and Apply and Close to quit.

Good, we have registered Oracle JDBC Driver manually. Let’s take a glance at Oracle Connector node again.

Perfect! Our Oracle Connector node has a Driver Name now.

To connect our database, we need to fill Hostname, Port, Database name and

Authentication with our database information and our credentials.

Having configured database information and authentication credentials correctly, please click Apply and see the traffic lights turn green. This means that we have now connected our database!

Let’s read a dummy table from our database.

Finally, we have reached our data! With DB Query Reader node, we can write SQL statements in KNIME, then click Evaluate to see our script results shown above.

Cheers!

Kerem