Accessing an Oracle database with LEAPWORK

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 we describe how to connect a test case in a LEAPWORK installation with a cloud based instance of an Oracle database.

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 setup the database instance exactly as described in these videos:

Creating a new Service Instance: https://www.youtube.com/embed/J2GNjALHaT4?rel=0&autoplay=1

Connecting to a Service Instance: https://www.youtube.com/embed/GUr0VLtupms?rel=0&autoplay=1

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:

ORACLE_HOME
TNS_ADMIN

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.

Download example file

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.

You can download the installation files from here: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

When you install it do NOT install it into the same directory as the Instant Client - this will not work. Just install it to a sibling folder (c:\ODP or similar).

To install ODP.Net run the install_odpm.bat file from a command-prompt running as Administrator. Specify the following parameters:

• Location of Oracle Instant Client (c:\InstantClient_11_2)
• x86|x64 depending on the OS
• true (specify true to setup ODP on the machine as part of the installation).

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.oca 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 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:

• DSN: name of the ODBC connection just created
• PWD: Password for the User ID specified in the ODBC connection

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.oca file).

Example:

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.