How to connect to ORACLE Database

Hi @Yumwzsch

I have found some examples and possible solutions here:

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

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

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

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

i have this problem


image
and with odbc
image

my obdc config


image

1 Like

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

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

1 Like

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

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

Hi Richard,

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

Thanks

1 Like

Hello friends,
Sometimes, in order to do the right test, it’s necessary to handle with test data.In almost all kind of applications, the data is stored in a database.

There is a database library for Robot Framework written by Franz See available

To install this library, the easy way is to use easy_install (or pip) to get the job done:

easy_install robotframework-databaselibrary

Once installed, there are a set of interesting available keywords that allows to connect and interact with a database list of keywords available
Note that to connect to some database engine it’s required a database interface.
In this example It’s being used the ox_Oracle interface.

After the setup it’s time to use the library. Don’t forget to add the library to the project:

Library DatabaseLibrary

At this moment, all library keywords are available and could be used. If you use Ride you need to re-open it after the installation.
To connect to the database should use one of the following keywords:

Connect To Database
Connect To Database Using Custom Params

The difference between both is that the second receives a connection string while the first one receives the decomposed variables of the connection string one by one.
I got a connection to my Oracle database instance using this:

Connect To Database Using Custom Params cx_Oracle user='user',password='pass',dsn='DSN'

After the connection I need to execute a set of sql instructions in order to prepare my tests. So, there are two possibilities. Use the available keywords Query or Execute Sql Script .

Using Query keyword${queryResults}= Query select * tableLog ${queryResults}

Using a sql fileExecute Sql Script c:\\file.sql

It’s a good practice always close the database connection:

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

8 Likes

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

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!

Where is the TNSNAMES.ora file located?

Try performing a TNSPING in the command prompt.
Link:- https://www.orafaq.com/wiki/Tnsping

Hello @Prashant_08,

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

Thanks for replying!
Gunjan

1 Like

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

Hi,

I am trying Connecting UiPath with Oracle
1.I have Downloaded the client of the same version as Oracle DB. (In this case, it was 12.1.0.2.0)
2. Installed InstantClient.
3. Created 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)
)
)
4. Placed the tnsnames.ora file under the instant client folder


5. Created an environment variable TNS_ADMIN with the value instant folder name
6. Open ODBC Data Sources (64-bit) from the Start Menu.
7. From the User DSN tab, clicked on Add.
8. From the data sources, I selected Oracle in OraClient12Home3_32bit. (Which have been created automatically)
Given the TNS Service and username and password as given in the TNSnames.ora but it showing the following error
Capture111

Thanks in advance

@logerror
@lakshman