How to connect to oracle database using VBScript and UFT?

It seems the question on how to connect to oracle database from UFT/VBscript is asked on multiple forums. In most places, there are answers in bits and pieces, without any one detailed answer that helps user get this information.

I spent close to 6 days to get this working and thus I feel its worth to share all the detailed steps here. The main reason I found this for going wrong is a wrong combination of one or more of below:

• Using the wrong driver.
• Using the wrong connection string.
• Using the wrong terminal to test the code (i.e. 64 bit to test working for a 32 bit UFT)

When any one of the above is wrong, we get a range of errors depending on which driver, connection string and terminal we are using. I encountered:

• Provider cannot be found. It may not be properly installed (when using Microsoft ODBC for Oracle)
• The 'OraOLEDB.Oracle.1' provider is not registered on the local machine (when tried using oraOLEDB driver)
• Catastrophic failure. (when running a working script on 64-bit terminal)

Hope below answer will give you a step by step answer with references, and code examnples to try out your code and get this up and running for you.

• It took me 5 days to get this correctly working and although there is information available on internet, its all scattered and leads to multiple directions, which can be very time consuming and frustrating. So to save you from this frustration and also since test data is very crucial for setting up any meaningful test framework, this section is dedicated to making this properly set up for you. Hopefully, you will not have to face the same frustration that I went through in dealing with this!

• To make oracle database connection work in UFT, download a 32-bit driver for your oracle database. To do this:

  • Go to this page (if your database is 12c - if another database, go to that page), https://www.oracle.com/database/technologies/oracle12c-windows-downloads.html Scroll down and look for "Oracle Database 12c Release 2 Client (12.2.0.1.0) for Microsoft Windows (32-bit)" - If you are working on oracle 12C
  • Download the zip file "win32_12201_client.zip"
  • Extract the file and run "setup.exe" from the folder : Downloads\win32_12201_client\client32
  • While selecting "What type of installation do you want",
    • select -> Administrator (1.5GB)
    • Use windows built in account (3rd option)
    • Keep default locations for
      • oracle base -> C:\app\client\yourUserID (no space in between allowed)
      • software location -> C:\app\client\yourUserID\product\12.2.0\client_2
      • Save response file for your future reference
      • Install the product
• To test if everything went okay or not, open the ODBC connection for 32 bit
• Check in the driver's section, if a driver with name "Oracle in OraClient12Home1_32bit" is installed or not.
• Now you can run the vbscript for testing database connection by running it in 32-bit command line mode. (Running it in a terminal on 64 bit machine would give you all sort of errors). To do this on windows,
  • Type %windir%\SysWoW64\cmd.exe in Start Search box.
  • Change directory to your script location (say cd c:\Users\yourUserID\UFT\Rusty\FunctionLibrary) - If the script is in dir FunctionLibrary
  • Now you can run the script by writing cscript in front of it -> cscript database-functions.vbs
  • You should see values popped up from test script.
• Connection string format:
  • https://www.connectionstrings.com/oracle-in-oraclient11g_home1/ (use standard format from here)
  • Dim connString: connString = "DRIVER={Oracle in OraClient12Home1_32bit};DBQ=yourHostURL:portNr/DBname;Trusted_Connection=Yes;UID=MyUser;Password=myPassword"
  • Ex (with dummy values): connString = "DRIVER={Oracle in OraClient12Home1_32bit};DBQ=ab12.mycompany.com:1521/ORAB;Trusted_Connection=Yes;UID=pramod;Password=myPassword"
• A script that you can use to test this connection is here.
  • https://github.com/PramodKumarYadav/Rusty/blob/master/FunctionLibrary/test-data-functions.vbs
  • Or here: https://hoopercharles.wordpress.com/2009/12/12/simple-vbs-script-to-retrieve-data-from-oracle/
  • Change the connection string to as mentioned above.

Hope this helps and you can save a lot of time and energy in using oracle connection itself to build tests, than setting this up itself.