Configuration of an Oracle ODBC data source


SelectCompare can be used to fetch data from multiple data sources, as long as there is an OLEDB or ODBC driver available for them. One of the most important database technologies on the market is undoubtedly Oracle and SelectCompare is able to use Oracle as a data source without any problems.

This post outlines the process of installation and configuration of the Oracle ODBC driver, setup of the ODBC data source and configuration of a connection in SelectCompare.

If you wonder how the SelectCompare might be used with Oracle, have a look at the second use case from this post.

 

Download and install the Oracle ODBC driver

For this post I chose the 32bit InstantClient implementation of the driver.

Go to https://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html

Select the Oracle Database Client (12.1.0.2.0) for Microsoft Windows (32-bit)

Screenshot of the Oracle ODBC driver download page

When the package is downloaded, run the setup.exe. An installation wizard will appear, guiding you through the process of installation.

On the first screen, select InstantClient option and click Next.

Oracle ODBC driver installation - initial screen of the installation wizard.

Follow the installation process using default settings.

Oracle ODBC driver installation - summary of features that are being installed

Oracle ODBC driver installation - setup progress dialog

If everything went fine, you should see the confirmation of the successful installation of the driver on your workstation.

Oracle ODBC driver installation - completion of the setup process

 

Setup an ODBC connection

First we will start with setup of the ODBC data source in the ODBC Data Source Manager. Note that you need to have your TNSNAMES.ORA file in working order.

Open ODBC Data Source Administrator (32-bit) and click "Add" button.

Oracle ODBC connection setup - system dialog showing configured ODBC connections

Select the appropriate driver from the list that will appear. Click "Finish" button.

Oracle ODBC connection setup - selection of the Oracle ODBC driver

Enter the name of the data source, any description you might find useful, the TNS service name and the username. Click "Test Connection"

Oracle ODBC connection setup - editor of the Oracle ODBC connection properties

Enter user password when prompted and click OK:

Oracle ODBC connection setup - entering password for the connection

When everything is set up correctly, you should see a confirmation of the successful connection. Click OK.

Oracle ODBC connection setup - testing connection

 Click OK on the Oracle ODBC Driver Configuration screen.

Oracle ODBC connection setup - completing the setup of the conneciton

You should see the newly created data source on the main screen of the ODBC Data Source Administrator. Click OK - you are ready to use the data source in SelectCompare!

Oracle ODBC connection setup - screen showing newly created ODBC connection

Set up Oracle connection in SelectCompare

 Finally, you are ready to set up a connection in SelectCompare.

Open SelectCompare and switch to the Connections screen.

Click the "Create new connection" button:

Create new Oracle connection in SelectCompare - add new connection

Enter the name of the connection and choose its color. Then click CREATE button.

Screen in SelectCompare showing unfilled connection details

Select “Microsoft ODBC Data Source” and select the provider displayed on the screenshot. Click OK.

Selection of the previously created ODBC connection in SelectCompare

Select the ODBC connection that you created in the ODBC connection manager.

Verification of the connection details in SelectCompare using system dialog

Enter username and password and click Test Connection.

Configuration of the Oracle ODBC data connection details in SelectCompare

The connection should succeed. Click OK.

Verification of the connectivity of the ODBC data connection in SelectCompare

Click OK on the connection properties dialog.

Completion of the configuration of the Oracle ODBC data connection in SelectCompare

You can also test the connection in SelectCompare:

Testing new Oracle ODBC data connection in SelectCompare user interface

Ensure that you save the connection before you exit the screen.

Saving new Oracle ODBC data connection in SelectCompare

Use connection in your comparison project

Now you are ready to use the defined connection in your project.

Create new or open an existing project in SelectCompare. Enter the name of the project and select a color you like. Click Edit button for the Source Query.

Use Oracle ODBC connection in SelectCompare - creating new project

Click CHANGE button on the Edit Query screen:

Editing new query using Oracle ODBC connection in SelectCompare

 Select the Oracle connection you created before:

Selection of the Oracle ODBC data connection in SelectCompare user interface

Enter your query to the edit box and run it to test the results.

Entering new SQL query using Oracle ODBC connection in SelectCompare

Navigate back to the comparison project screen and configure the rest of the parameters.

Configuration of the data comparison project parameters.

 

That's it! 

Please do not hesitate to reach out to us if you have any questions or suggestions.

 

What next?

SelectCompare can be used to compare your data not only between Oracle tables and databases, but also with other data sources. For example you can have an Excel spreadsheet or a CSV file that you would liket to compare with your data in Oracle. SelectCompare can do it - and it costs nothing to download an test it for a while - why wouldn't you do it?

Some relevant information can be found below - have a look at it!


Time is money

Write your queries with filters and aggregations and receive the comparison results. Reuse the queries and comparison definitions - save time!

Try SelectCompare today!


Choose your edition



Leave a comment


Please note, comments must be approved before they are published