Using QTP to Connect to an Oracle Database with Instant Client

By gopherr | June 26, 2008

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

Data Source Name (DSN) Setup:

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

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};DBQ=<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)

)

)

Topics: Code, QuickTest Pro | 15 Comments »

15 Responses to “Using QTP to Connect to an Oracle Database with Instant Client”

Leo Says:
February 17th, 2009 at 6:06 am

Great description, this will be of great use in my current QTP project.

One question:
How do you ensure you’re using the correct ‘tnsnames.ora’ file you’ve placed in \NETWORK\ADMIN? Should that directory be added to the environment variable as well?

Thanks!

gopherr Says:
February 17th, 2009 at 7:44 am

Thanks Leo!

To ensure you have a working tnsnames.ora file (or more importantly, the information within) you should contact your database administrator to make sure you have access to the database and to get the server name/IP address and port used. Also, you can perform an internet search for ‘tnsnames.ora examples’ to see several samples on the web.
The \NETWORK\ADMIN directory does not have to be added as an environment variable since it is located within the ORACLE HOME (e.g. C:\Oracle\instantclient10_2) directory you specify and list as an environment variable.

I hope that helps!
Geoffrey

Lionel B Says:
July 22nd, 2009 at 3:43 am

Very helpful!
Warning: a little misprint in the connection string!!!
“DBQ=;” rather than “DQB=;”

Lionel.

Yathish Says:
December 17th, 2009 at 6:12 am

Hi Geoffrey,

I have QTP installed in remote machine and also toad (oracle client) on the same machine, i want to connect to toad from QTP,

I executed the script given in this blog, but its displaying an error message as “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”,

Can you please help out,

Thanks,

XeroCube Says:
December 17th, 2009 at 10:51 am

I have updated the post to correct the Typo that Lionel pointed out as well as another typo.

Vishnu Says:
February 23rd, 2010 at 8:58 am

Hi All,

I tried to connect my DB via QTP. I used the below Script in QTP for connecting and querying DB. Iam gettign this below error in dialog box
“[Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error
Line (22): “conn1.Open”.

Note: First I used one host in connection string. Still I got the same error. I also set up “MYDB” datasource in “System DSN” tab on Control Panel -> Administrative Tools ->Data Sources (ODBC) for “Oracle in OraClient10g_home1” driver. I tested the connection after setting uphere and the connection was successful. I am able to connect to MYDB through TOAD application.

Please advice me how to correct the script.

************Starting of Script******************

thisUser = username
thisPW = password
Environment(“CURRENT_SERVICE_NAME”) = “MYDB_APP.company.net”
sql = “select * from my_table’;”
ConStr = “Driver={Oracle in OraClient10g_home1}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS = (PROTOCOL = TCP)” & _
“(host = abcn01p.company.net || host = def03v2.company.net)(PORT=1521))” & _
“(CONNECT_DATA=(SERVICE_NAME = ” & Environment(“CURRENT_SERVICE_NAME”) & “))); uid=” & thisUser & “;pwd=” & thisPW & “;”

Set conn1 = CreateObject(“ADODB.Connection”)
Set thisObjRS = CreateObject(“ADODB.recordset”)
conn1.ConnectionString = ConStr
conn1.Open
Set thisObjRS = conn1.Execute(query,,adExecuteNoRecords)
varArray = thisObjRS.GetRows

**************End of Script******************

My TNS.Ora file is exactly same as below.

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(host = abcn01p.company.net)(port = 1521))
(ADDRESS = (PROTOCOL = TCP)(host = def03v2.company.net)(port = 1521))
)
(CONNECT_DATA =
(service_name = MYDB_APP.company.net)
)
(source_route = yes)
)

Bramesh Says:
July 5th, 2010 at 7:26 am

Hi

In field PWD= while giving the password the other person who is executing the script can see the password.

Can we get the password here in encrypted form.

Silambarasan.Viswanathan Says:
September 19th, 2011 at 5:40 am

Thanks…

Rajesh Says:
January 10th, 2012 at 9:31 am

Hi Every one,

I am not able to connect to database is there any mistake in this code… I am getting this error ‘Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error’

sql = “select * from my_table;”
ConStr = “Driver={Oracle in OraClient10g_home1}; CONNECTSTRING=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=uat-erpdca01.xxxxxxxxx.com)(PORT=xxxxx)))(CONNECT_DATA=(SID=UAT)); (UID=appsxx); (pwd=xxxxx3))”
Set conn1 = CreateObject(“ADODB.Connection”)
Set thisObjRS = CreateObject(“ADODB.recordset”)
conn1.ConnectionString = ConStr
conn1.Open
Set thisObjRS = conn1.Execute(query,,adExecuteNoRecords)
varArray = thisObjRS.GetRows

CPham Says:
January 16th, 2012 at 4:26 pm

Hi I followed the directions above and got this error:

[Oracle][ODBC][Ora]ORA-00604: error occurred at recursive SQL level 3
ORA-08243: recursive audit operation attempted
ORA-08243: recursive audit operation attempted
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 3
ORA-08243: recursive audit operation attempted
ORA-08243: recursive audit operation attempted

Line (25): “objConn.Open strConn”.

Line 25 is: objConn.Open strConn

I googled and didn’t find anything that would help me. Does anybody know what these error means?

Thanks for you help.

sayeeda Says:
June 12th, 2012 at 12:53 am

Hi anyone

pls can u help me to connect db i m usng toad

sayeeda Says:
June 12th, 2012 at 12:56 am

Hi anyone

pls can u help me to connect db i m usng toad
..
my db is oracle instant 11g
TNS is
GNITGW=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=gcu21347.austin.hp.com)
(PORT=1526)
)
(CONNECT_DATA=
(SERVICE_NAME=NEWTIC)
)
)

sayeeda Says:
June 12th, 2012 at 12:58 am

please can u mail me any script tht works to connect db
my id is ksayeedanikhath@yahoo.co.in

pooja Says:
May 27th, 2013 at 3:42 am

Hi,
I have QTP installed in remote machine and also toad (oracle client) on the same machine, i want to connect to toad from QTP,

I executed the script given in this blog, but its displaying an error message as “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”,

Can you please help out,

Thanks,
Pooja

Anurag Says:
June 12th, 2013 at 1:16 am

Hi ,

I am trying to access Oracle database via QTP 9.5 and my script works for me but the only issue is that sometime QTP get hang while making a connection and even though i kill the process it still keep on running and i have to restart the system. Please advise what can be the issue because this behavior is random and i am not able to figure out the exact issue.

Comments

Archives

Blogroll

RockinMedia