How to connect to ORACLE Database

[rm21]

Hello guys,

I’ve been trying to connect my robot to an Oracle database.
I read one of the articles under “Knowledge Base” called “How to query data from a SQL database” which states the workflow to query a DB, I cant really see anything that helps to connect to the DB, rather than this:

“A. First connect to the database using the Connect activity. This activity requests a connection string used to establish a database connection and a ProviderName. The string must contain a data source, a user name and a password”

However is vague and it does not justify what the String format should look like.

I need some insights from someone who managed to successfully connect to a DB with a String example and a connection setting summary

Best regards
Rom

[aksh1yadav (AKSHAY YADAV)]

@rm21

However is vague and it does not justify what the String format should look like.

I need some insights from someone who managed to successfully connect to a DB with a String example and a connection setting summary

This is already asked previously in the forum so next time please make a search before posting and for now hope this link will help you on this

Error connecting to database

Hi, Can you use the Microsoft Driver syntax for ODBC in the connection string and try? Below is an example. I am guessing the issue might be because you have used the DSN syntax. “Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=UserName;Pwd=Secret;” Regards, Amrita

Regards…!!
Aksh

[rm21]

Thank you Aksh for your quick reply.
I followed your advice:

1. 

2. 

3. 

NOTE: Connection String = Driver={Microsoft ODBC for Oracle};Server=10.3.4.xxx;

4. After clicking test connection thats the error I’m getting:

5. I have downloaded Oracle’s Instant Client and ODBC Driver as you can see in the screenshot below

6. And I have set the Enviroment Variable PATH to the directory of the Oracle Instant Client extraction.
   Still it does not work. Can you please be more specific what am I doing wrong?

[richarddenton]

Does this help?

http://stackoverflow.com/questions/13626024/oracle-odbc-drivers-sqlallochandle-on-sql-handle-env-failed

[c_cumplido]

Hello,

Following this thread, I am also trying to connect UiPath to an existing Oracle database. To do so, as said in previous posts, it is necessary to enter a string indicating user name, password and provider. I know the structure of the string but I don’t know exactly what provider means. Besides this, is it necessary to install a Oracle client to make the connection?

Thank you.
Regards,

[richarddenton]

Late response but check out this link for Oracle connection strings

https://www.connectionstrings.com/oracle/

[Yumwzsch (Mikkel Rønde Jensen)]

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

[esa.andsten]

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”

[Yumwzsch (Mikkel Rønde Jensen)]

@esa.andsten

Create System DSN data source in the ODBC Data Sources (done by an Oracle expert)

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

[ovi (Viorela Preda)]

Hi @Yumwzsch

I have found some examples and possible solutions here:
https://stackoverflow.com/questions/10786782/ora-12514-tnslistener-does-not-currently-know-of-service-requested-in-connect-d

[esa.andsten]

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

[msusila (Susila)]

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.

[vishal.kumar (Vishal Kumar S)]

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

[richarddenton]

Make sure you don’t hard code your passwords

[luchovelez (Luis Velez)]

@vishal.kumar

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

i have this problem

and with odbc

my obdc config

[esa.andsten]

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

[luchovelez (Luis Velez)]

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

[SuryanarayanaMatta (Surya)]

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?

[SuryanarayanaMatta (Surya)]

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)

Connecting to Oracle Database

Hi, Alex and fahuet i want to connect with oracle database 12c with the following String. It doesn’t work. I am not sure if the string needs to be modified. i want to know your opinion. “Data Source=OracleDatebaseTest;User ID=c##uipathrobot;Password=[xxxxx]” with best regards

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

[Prasanth (Prasanth Thiagarajan)]

Hi Richard,

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

Thanks