QTP – How to Connect To an Oracle Database

PerfectoTopAd

Why this post?

In a previous post on QTP Database Testing I gave examples on how to connect to MySql, Sql server and Excel databases. Since then I’ve received a few questions on how-to do the same for an Oracle database.

How does this work?

In general QTP can use database resources already installed on most computers. The most common are ODBC and ADO and both can be used to communicate with a database using VBScript code in QTP.

How to Connect to Oracle using an OLE DB Provider

Dim myConnection, myRecord
Set myConnection = CreateObject("ADODB.Connection")
myConnection.Open "Provider=MSDAORA;server=TDORASERVER;user id=system;password=manager"
set myRecord=CreateObject("ADODB.recordset")
sql = "SELECT * FROM bob_ora_db.USEMYRECORD WHERE US_USERNAME='admin'"
myRecord.Open sql, myConnection
for each r in myRecord.fields
   msgbox r.name
next

How to Connect to Oracle using a MS ODBC for Oracle

Dim myConnection, myRecord
Set myConnection = CreateObject("ADODB.Connection")
myConnection.Open "Driver={Microsoft ODBC for Oracle};" & "Server=TDORASERVER;" & "Uid=system;" & "Pwd=manager"
set myRecord=CreateObject("ADODB.recordset")
sql = "SELECT * FROM bob_ora_db.USEMYRECORD WHERE US_USERNAME='admin'"
myRecord.Open sql, myConnection
for each r in myRecord.fields
   msgbox r.name
next

Unable to connect?

If you have issue always remember that the best person to ask would be your Oracle admin. He/She should be able to help you create a connection string if the two examples above do not work for you.

7 comments
byzoor - December 19, 2011

Hi Joe, thanks a lot for sharing information, Could you plz publish for connect QTP and IBM DB2. Thanks in advance. Thanks a million for sharing Connection string concepts.

Reply
Brian White - January 15, 2013

Hey Joe,
Thank you for posting this. And I had a couple questions about these connection strings. First off, what is the “Provider” pointing at; for example, what is MSADORA? Some sort of process running on my system? I am trying to figure out where to look for things like Provider. Second, is the datasource the name of the database or the host URL or IP? I am completely comfortable using the MS SQL connection string but Oracle has been killing me.

Reply
    Joe Colantonio - January 17, 2013

    Brian White » Hi Brian – MSADORA is a DLL for Microsoft Data Access – OLE DB Provider for Oracle. It is included with the Microsoft Data Access Componet (MDAC) is installed on a machine. The dlls are located in the C:\Program Files\Common Files\System\Ole DB directory.

    A datasource name is usually defined in a machine’s ODBC Data Source Administrator User DSN and SYSTEM DSN tabs. Does this make sense? Cheers~Joe

    Reply
Kash - April 2, 2013

Do we need to commit after deleting records in the oracle database. If yes how we do it?

Reply
Manu - June 2, 2015

Hi Joe, first of all, lot of thanks for sharing your knowledge!

We have a question regarding database, we have allocated a Access Database on ALM – Resources.

We can download it to local (in your post https://www.joecolantonio.com/2010/08/17/how-to-save-and-retrieve-a-qc-test-resource/)

but it should be possible to connect to the Database directly although the “server” that the database is allocated is ALM?

Something like:

myConnection.Open “Driver={Microsoft Access Driver (*.mdb, *.accdb)};” & “Server=ALMServer;” & “Uid={ALMUser};” & “Pwd={ALMPassword}”

thanks for help and support and congrats for your page!

Reply
George - February 17, 2017

Hey J C – Can we save the query content from a DB recordset object in to an Excel or CSV file formats? Do we have any working methods available like recordsetObj.Save , .Saveas or .saveTofile !

Appreciate your help here!!

Reply
Click here to add a comment

Leave a comment: