How to connect to ORACLE Database

Help
,
8

Hi Sharon

I have similar issues failing to understand how to set a oracle db connection up.
But my question is: Do I have to the client installed for this to work?

Kind regards Mikkel

9

This is what I had to do to get a working connection to a Oracle database in a Windows Server 2012 R2 server.

  1. Install Oracle Instant Client (done by an Oracle expert)
  2. Create System DSN data source in the ODBC Data Sources (done by an Oracle expert)
  3. Use DatabaseConnect activity in UiPath
    3a. Enter ConnectionString, for example “Dsn=YourDBName;uid=UserID;pwd=password”
    3b. Enter ProviderName “System.Data.Odbc”
10

I was able to install a Oracle client (both a x32 and a x64, since i got the error from this post: Connecting to Oracle Database) .

Now though i get the same error as described in this post: Error connecting to database but the solution doesnt help me. As far as I can read I have a problem with my ODBC, but what is the prerequisite for using this setup?

Alternately if I try to set up my connection with:
ProviderName: “System.Data.OracleClient”
ConnectionString: “SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myHostNam)(PORT=myPort))(CONNECT_DATA=(SERVICE_NAME=mySID)));uid=myUsername;pwd=myPwd;”
I get the error message:
“Connect : ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”

Got any pointers on what Im doing wrong?

Kind/desperate regards Mikkel

1 Like
11

Hi @Yumwzsch

I have found some examples and possible solutions here:

12

Yes please check your tnsnames.ora file that it contains valid information. Also test your DSN connection from the ODBC driver conf if possible. This example is from Win Server 2012 R2

odbc_conf
odbc_conf1152×648 9.67 KB

13

Installing the basic 32 bit oracle client and then adding the below to connection string worked for me.

“Data Source=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=<service_name>))(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)));Uid=;Pwd=;”

Choose System.Data.OracleClient from the dropdown.

Since it’s a basic client, and I am using the descriptive connection string, I didn’t have to include any .ora files at all.

1 Like
14

Hello,

Below is the correct way to connect UI Path and Oracle Database

“Data Source=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=<service_name>))(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)));Uid=;Pwd=;”

Thanks,
Vishal

1 Like
15

Make sure you don’t hard code your passwords :wink:

16

i have this problem

image
image1364×352 15.1 KB

image
and with odbc
image
image
image1433×338 16 KB

my obdc config

image
image1402×587 152 KB

image

1 Like
17

Hi, it looks like your connection is fine but there’s an error in your SQL query. You should be able to see the Oracle error message in UiPath Studio Output window. The Oracle SQL queries may need to be modified when you transfer them to UiPath, depending on what you want to do. For example double quotes need to be given as ascii character chr(34).

18

ther query is fine, the problem is the 64bit odbc

1 Like
19

Hi,

Am trying to connect DB from Instant Client.

I have updated connection string details as mentioned above, however am getting below error.
Is there any way I can resolve this issue?

image

2 Likes
20

I was able to configure Instant Oracle Client and able connect DB using Microsoft ODBC Driver.

Am trying to connect Database through Instant Client in UiPath.

As per the link, have also installed Microsoft visual C++ redistributable 64bit (not able to connect)

I have used below steps, but no luck still am not able to connect Database.

“Data Source=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=<service_name>))(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)));Uid=;Pwd=;”
System.Data.OracleClient
System.Data.ODBC

“Dsn=YourDBName;uid=UserID;pwd=password”
System.Data.ODBC
“Dsn=YourDBName;Driver=Microsoft ODBC for Oracle;uid=UserID;pwd=password”
System.Data.ODBC

image

21

Hi Richard,

How to use variables retrieved from assets and include them in the connection string?

Thanks

1 Like
23

Connecting UiPath with Oracle

  1. Go to the following link and download the 32-bit client. Make sure to download the client of the same version as Oracle DB. (In this case, it was 12.1.0.2.0)
    https://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html
  2. Install the downloaded client.
    From the installation options, choose InstantClient.
  3. It will probably be installed under C:\app\Adminstrator\product\12.1.0
  4. Create a tnsnames.ora file with the following data:
    auto_claims=
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=10.x x.xxx)(PORT=1521))
    (CONNECT_DATA=
    (SERVICE_NAME=auto_claims)
    )
    )
    Where,
    auto_claims = Service name
    10.x.x.xxx = Host name
    1521 = Port
  5. Place the tnsnames.ora file under C:\app\Adminstrator\product\12.1.0\client_3
  6. Create an environment variable TNS_ADMIN with the value C:\app\Adminstrator\product\12.1.0\client_3
  7. Add the following to path envrionment variable if not already added:
    C:\app\Adminstrator\product\12.1.0\client_3
  8. Open ODBC Data Sources (32-bit) from the Start Menu.
  9. From the User DSN tab, click on Add.
  10. From the data sources, select Oracle in OraClient12Home3_32bit. (It should have been created automatically).
  11. If you face any error, download and install the following:
    Microsoft Visual C++ 2010 x86 Redistributable – 10.0.xxxxx
  12. Click Finish.
  13. Enter the following values:
    Data Source Name: Any name of your choice
    TNS Service Name: auto_claims (same name from tnsnames.ora file)
    User ID: UserID (same from tnsnames.ora file)
  14. Click on Test Connection.
  15. It will prompt for a password. Enter the password and Click OK.
  16. It should say, Connection Successful.
  17. In UiPath Studio, take a Database Connect activity.
  18. Click on Configure Connection.
  19. Click on Connection Wizard.
  20. Click on Microsoft ODBC Data Source and Click OK.
  21. Select the DSN you created before from the Use user or system data source name dropdown.
  22. Provide User name and Password below that.
  23. Click Test Connection. It should be successful.
  24. Select the Provider as System.Data.Odbc
  25. Create a connection object and use it wherever you want!!

Regards,
Hiren

9 Likes
24

Nice details.
I had oracle express (as probably most people do, since it is free) and I configured it.
Just Instant Client should be in 32 bit not 64 bit (this happened to me).

I am going to try to configure the connection with one more way and maybe record it because it was pretty hard for me to configure all of that

25

Hello,

Has this worked for anybody? I’ve been able to test the connection successfully within Studio, however the workflow execution fails with the attached error. Thanks for your help in advance!

Capture
Capture862×370 19.2 KB

26

Where is the TNSNAMES.ora file located?

Try performing a TNSPING in the command prompt.
Link:- Tnsping - Oracle FAQ

27

Hello @Prashant_08,

I was able to resolve this using this thread:
[Connection to Oracle]

Thanks for replying!
Gunjan

1 Like
28

I type your mentioned below info into the CONNECT activity and it works to fetch data in Oracle databse. Hope it is helpful for everyone.

ProviderName: “System.Data.OracleClient”
ConnectionString: “SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myHostNam)(PORT=myPort))(CONNECT_DATA=(SERVICE_NAME=mySID)));uid=myUsername;pwd=myPwd;”

1 Like