Connection to Oracle

database
studio
connection

#1

Hello!
Someone could explain to me how to make a connection to Oracle step by step. I tried it by all means and I have not had any result

thank you very much


#2

@Daniel_Rodriguez May be you had found solution but connecting to Oracle is a critical task which took me 3 days to solve different kind of issues with different situation, so that I posted here (on your topic) may be it could help someone later.

Please noted that, until July of 2018, UiPath has only version 32bit, so that you need oracle InstantClient for 32 bit, not 64 bit.

For uipath, there are 2 ways to connect to oracle DB
(1) using Microsoft ODBC
(2) using oracle client

Method (1) Microsoft ODBC: works; up to Windows 8. For windows server 2012 and windows server 2016, you need method (2). Because (1) has been deprecated.

More detail on method (1) using Microsoft ODBC

NOTE: for windows 64 bit, if you follow these steps From Windows, click Start, then select Settings, Control Panel, Administrative Tools and Data Sources (ODBC), by default, it will open version for 64 bit. You MUST open 32 bit. In order to do that, please use this file:

C:\Windows\SysWOW64\odbcad32.exe

From that forward, you can create ODBC and ready for using UiPath to connect to it.

Within UiPath, you need to install database package (if you had not installed it), so that you have activity namely: UiPath.Database.Activites.DatabaseConnect

Configure that Activity with below information:

Connection String: “Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.x)(PORT=1521))(CONNECT_DATA=(SID=YOUR_SID)));Uid=USERID;Pwd=PASSWORD;”

Provider Name: “System.Data.Odbc

More detail on method (2) using OracleClient

I MUST use method (2) because ODBC does not work on windows server 2012 and windows server 2016

  • Follow guideline at (*) to install InstantClient

  • Open Uipath and use this activity: UiPath.Database.Activites.DatabaseConnect

  • Configuration Detail
    Connection String: “Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.x)(PORT=1521))(CONNECT_DATA=(SID=YOUR_SID)));Uid=USERID;Pwd=PASSWORD;”
    Provider Name: “System.Data.OracleClient”

More information and troubleshoot:
If after ( * ), and ( ** ) things does not work; in my case, I had installed more than (*) mentioned. Totally, I installed 3 packages:

  • instantclient-basic
  • instantclient-odbc
  • instantclient-sdk

With Method (2)

  • Becareful with version of instantclient, because with version 12 (e.g: 12.1.0.2.0 …) Method (1) works well on Oracle version 10 upward but it does not work for oracle version 9
  • Oracle version 9 requires you to use InstanceClient version 11 (e.g: 11.1.0.7.0 ). I had used InstantClient version 11.1.0.7.0 for both Oracle DB version 9, and version 10

With Method (2)

  • I did not create tnsnames.ora and and sqlnet.ora but it still works

With Method (1)

  • It works on windows 7, windows 8 but not work on windows server 2012, and windows server 2016
  • I had created tnsnames.ora and sqlnet.ora. But I’m not sure whether it is required or not.

With Visual Studio Redistributable:

  • You can install multiple versions on the same computer, they can coexist without any problem.
  • You have to install companion version of Visual Studio Redistributable for each InstantClient in order for it to work.

With connection checking:

  • Prepare your self another tool, like SQLDeveloper, for testing connectivity from your PC to oracle DB before configure with UiPath. Just to ensure that you can connect successful.

One last thing:
Be careful about connection string in 2 cases; case (2) does not have Driver within it.

More reference for issue of ODBC on windows server 2012, 2016:
https://social.msdn.microsoft.com/Forums/vstudio/en-US/6b79325e-dee5-4a55-9a1c-e5c01938fd86/oracle-c-connection?forum=csharpgeneral

https://support.microsoft.com/en-us/help/822841/fix-setting-of-connection-attribute-fails-when-you-use-connection-pool

https://stackoverflow.com/questions/29532949/error-while-connecting-to-oracle-database-in-windows-server-2012


#3

This is incredibly helpful, thank you for sharing with everyone.

Quick question on the connection string - How do you connect when username/password are not used due to SSO? I tried simply removing Uid and Pwd, but got an error stating i had an invalid username/password. I am able to log on via PL/SQL Developer without inputting anything in the username/password fields


#4

replying to myself, but I just replaced Uid=USERID;Pwd=PASSWORD; with Integrated Security=yes and it is working