How to connect to ORACLE Database

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

7 Likes

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 :slight_smile:

Regards…!!
Aksh

1 Like

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

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

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

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

  1. 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?

Does this help?

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

2 Likes

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,

1 Like

Late response but check out this link for Oracle connection strings

2 Likes

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

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”

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

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