QTP Integration with Oracle DB

You can connect to Oracle database and execute SQL statements by following steps:

• Oracle client has to be installed on QTP (or UFT) host.
• Assuming the tnsnames.ora file contains the desired database information.

Using following two functions can help you to execute SQL queries.

Function executeQuery(p_db, p_sql) Reporter.ReportEvent micPass, "executeQuery", "The SQL query: '" &p_sql & "' is going to be executed on: '" & p_db & "'." If getVal(p_db)="N/A" Then Reporter.ReportEvent micFail,"executeQuery","DB connection information is not available." executeQuery = -1 ExitAction End If Set localConn = CreateObject("ADODB.Connection") Set localRs = CreateObject("ADODB.Recordset") Set localErr = CreateObject("ADODB.Error") On Error Resume Next localConn.open getVal(p_db) Global_DBErr = "" For Each localErr In localConn.Errors if Err.Number<>0 then Global_DBErr = Global_DBErr & Err.Number & vbCr & " " & Err.Description Reporter.ReportEvent micFail,"Step 1","DB connection failed: " & Global_DBErr ExitAction end if Next On Error Resume Next localRs.open p_sql, localConn For Each Local_Err In localConn.Errors if Err.Number<>0 then Global_DBErr = Global_DBErr & Err.Number & vbCr & " " & Err.Description Reporter.ReportEvent micFail,"Step 1","SQL Execution Failed: " & Global_DBErr ExitAction End if Next if localRs.eof then v_retVal = -1 else v_retVal = localRs(0).value end if set localConn = Nothing set localRs = Nothing set localErr = Nothing Reporter.ReportEvent micPass, "executeQuery", "Query result: [" & v_retVal & "]" executeQuery = v_retVal End Function

Function getVal(p_key) v_retVal = "N/A" Select Case p_key Case "DB1" v_retVal = "Provider=MSDAORA.1;Password=DB_PASWORD1;User ID=LOGIN_USER1;Data Source=DB1;Persist Security Info=True" Case "DB2" v_retVal = "Provider=MSDAORA.1;Password=DB_PASWORD2;User ID=LOGIN_USER2;Data Source=DB2;Persist Security Info=True" End Select getVal = v_retVal End Function

How you make use of these functions is as following:

 v_sysdateFromDB = executeQuery(Parameter("p_db"), "select sysdate from dual")