Accessing an Oracle database with Leapwork using ODBC

Accessing an Oracle database with Leapwork using ODBC

Introduction

If you want to use the ODBC based Database block in a Leapwork flow to access an Oracle database, you typically have to run through a few steps to make it work. In the following article, you will learn how to connect a test case in a Leapwork installation with a cloud-based instance of an Oracle database.

Prerequisites

ODBC driver with a 64-bit version must be installed on your system (please install AccessDatabaseEngine_X64.exe from here).

Database Instance

We have a database instance ( 11g_release2) running on oraclecloud.com. We have opened for database access on port 1521 in the list of access rules:

We have set up the database instance exactly as described in these videos:

ODBC Client

The database block in Leapwork uses ODBC connections to connect to databases. In order to connect to an Oracle instance you need to install the Oracle ODBC client. This requires both the “Instant Client Package - Basic” and the “Instant Client Package - ODBC” client. Both can be downloaded from here: http://www.oracle.com/technetwork/topics/winx64soft-089540.html for various versions.

Follow this short guideline on how to install the Oracle ODBC Client (Instant Client): http://www.oracle.com/technetwork/database/features/oci/odbc-ic-releasenotes-094306.html.

In the following, we assume that the Instant Client was installed at c:\InstantClient_11_2.

System Variables

Add the following System Variables:

Both variables should be set to the path to the location of the Instant Client (c:\InstantClient_11_2).

You can find a description of how to add the variables for various versions of Windows here: https://www.computerhope.com/issues/ch000549.htm.

tnsnames.ora

Add a tnsnames.ora file to the installation directory for the ODBC client (c:\InstantClient_11_2). Just create it as a text file and change the file extension.

Be aware that the structure of the file is intact - it seems a bit sensitive to changes:

Typically, you will need to change the HOST and the PORT to point to your local database instance and change the SID to point to the right database.

ODP.NET

Oracle Data Provider for .Net (in short ODP.NET) is the glue between a .Net based application - like Leapwork - and the Oracle Instant Client (ODBC client). This needs to be installed in order to access the Oracle ODBC connections.

here: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

When you install it

The final statement for the installation could look like this:

ODBC Connection

Now all the installation is done, and it's time to setup an ODBC connection. In the start menu in Windows type "ODBC" and select the 64-bit ODBC administrator. If you have installed Leapwork 32-bit select the 32-bit administrator.

In the ODBC administrator you can create either a User or a System DSN. Leapwork can work with both types. Click 'Add' to add a new data source and select the 'Oracle in instantclient_11_2' (depending on the version of Instant Client) and click 'Finish'.

This will open the details form for the data source. Name the data source and select the TNS service name from the dropdown. The dropdown contains the services specified in the tnsnames.ora file added previously. If you haven't set up the System Variables correctly this dropdown can be empty.

Specify a User ID and click 'Test Connection'. Enter the password for the user and click 'OK'. This has to work in order for Leapwork to be able to use the connection!

Leapwork

Version

Make sure that your installed version of Leapwork is 2.0.708 or higher to include support for specifying ODBC parameters and part of the connection field.

Connection

Add a database block to a flow and add the following information to the connection field:

The value in the connection field should be specified as: DNS=ODBCNAME;PWD=PASSWORD

Example

Query

Specify a query to read, insert, update or delete data in the specified database (SID in the tnsnames.ora file).

Here's the example flow:

If you experience problems during the execution of the query and when you click refresh to retrieve the columns, you can find more information about the error in the studio.log file (c:\Program Files\LEAPWORK\Logs\studio.log). It can also be a good idea to verify the query outside Leapwork - either via a DBA team or in a local query tool - if you are having problems with the database block.

If you have any questions, please reach out to our Priority Support team.