Using QTP to Connect to an Oracle Database with Instant Client

by Geoffrey Rodgers
Need a light-weight ODBC connection to an Oracle database for you QTP scripts? Oracle Instant Client is just the ticket, assuming you have Oracle version 10g for your database. Please read the information provided by Oracle to make sure Instant Client is right for your needs.
The Instant Client Driver

I’m not going to rewrite everything provided by Oracle, but I will quickly say (for those of you that did not click on the above link) that Instant Client is a very quick installation and contains less overhead than the full client software that was previously required to connect to an Oracle database using something other than the Microsoft ODBC for Oracle driver.
Instant Client Driver Setup:

Download the version of Instant Client for your database version. In the example below, we’ll be using Oracle version 10.2.0.4 for Microsoft Windows (32-bit).

• Following the directions from Oracle, we see that Instant Client Package - Basic is required, then we can add Instant Client Package - ODBC on top of that to accomplish our goal of an ODBC connection.
• Unzip both packages to the directory of your choice. Here, I’m unzipping both packages to the C:\Oracle\instantclient10_2 directory.
• Once BOTH packages are unzipped to the same directory (i.e. all files are in the same folder), double-click the odbc_install.exe file.
• After the driver is registered on your machine (you may not get a confirmation dialog, just a blip on the screen that was your command prompt), you will need to create a TNS names file that will be referenced by the driver at the time of attempting the database connection. These files contain all the relevant connection information for one or more of your data sources. Each ’set’ of information is referenced using a TNS name that you specify.
• Within the directory where you unzipped the Basic and ODBC packages, you will need to add a subdirectory where Oracle will go in search of the TNS names file. In this example, I’m creating the folder structure like so:
  • C:\Oracle\instantclient10_2\NETWORK\ADMIN
  • Add a file called tnsnames.ora in the ADMIN folder. Below, I will show example contents of a TNS names file.
• Now we need to add an Environment Variable pointing to the directory where everything was unzipped (e.g. C:\Oracle\instantclient10_2)

Data Source Name (DSN) Setup:

You will need to create a new DSN if you plan on using QTP’s Database Checkpoint functionality.

• Click Start, point to Control Panel, double-click Administrative Tools, and then double-click Data Sources(ODBC).
• On the User DSN tab, click Add.
• Select Oracle in instantclient10_2 from the list, then click Finish.
• In the Data Source Name field, enter any easy-to-remember name you wish.
• In the TNS Service Name field, enter the name of the TNS setup located in your tnsnames.ora (again, I have included an example below).
• Click Test Connection, enter the user credentials (name and password) that have been granted access to the Oracle database to which you are trying to connect, then click OK.
• If the test is successful, your driver is working correctly! The DSN name you provided will be what you look for when creating a database checkpoint in QTP.

Reading from the Database in QTP:

The following sample code (or some form of it) is readily available in most QTP or VBScript help forums, so don’t dispair if I’m not giving you exactly what you need.


Dim objConn ' As an ADODB Connection object

Dim objRecSet ' As an ADODB Recordset object

Dim strConn ' As a connection string

Dim strQuery ' As a SQL query

' Create the connection and recordset objects

Set objConn = CreateObject("ADODB.Connection")

Set objRecSet = CreateObject("ADODB.Recordset")

' Create the connection string

strConn = "DRIVER={Oracle in instantclient10_2};DQB=<YourTNSName>;UID=<YourUsername>;PWD=<YourPassword>"

' Define the SQL query

strQuery = "SELECT * FROM <YourDatabaseTable>"

' Open the connection

objConn.Open strConn

' Retrieve data using SQL query

objRecSet.Open strQuery, objConn

' Do something with the data here

' Close the connection (also closes the recordset and destroys both)

objConn.Close

Sample TNSNAMES.ORA Content:

>YourTNSName =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = <YourServerNameOrIP>)(PORT = <YourPortOrDefault1521>))

)

(CONNECT_DATA =

(SID = <YourDBSID>)

(SERVER = DEDICATED)

)

)