Getting Data from Oracle Database

[sonuiiml]

HI Team, 

I am trying to retrieve data from Oracle database through OLEDB object. I am able to access the required tables from Oracle SQL developer. I did tried giving the connection string in OLEDB object how ever it gives the error as OLEDB data provider cannot be located.  My question

1. Is it possible to get the connection string from Oracle SQL developer tool to be used in blueprism (currently I am able to access the data from Oracle SQL developer tool)?
2. Do I need to install Oracle Data access components separately?

Please let me know if you have any questions

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

[ewilson]

@Susamay Halder,

The problem is in how you're trying to download the file from GitHub. You're grabbing it from the regular browser display which decorates the file with HTML tags.

Take a look at this post about how to work with repositories on GitHub.

https://community.blueprism.com/communities/community-home/digestviewer/viewthread?GroupId=385&MessageKey=516d01d6-82b1-473b-8e69-057c50e9fb51&CommunityKey=1e516cfe-4d1f-4de9-a9eb-58d15bf38c81&tab=dig...​

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[sonuiiml]

@ewilson
I did tried to import by using the steps mentioned and also by downloading the zip and unzipping file but now it gives below error

​​any thoughts? Let me know if you need more details 

​​​

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

[ewilson · Accepted solution]

@Susamay Halder,

I believe I figured out the issue. In BP v6.0 there was no support for environment variables defined as Password data types. The Oracle MDA integration has an environment variable of Password type. I've manually updated the attached .bprelease to set that data type to Text, and I was able to import the file into an instance of BP v6.

Just change the file extension from ".txt" to ".bprelease".

Cheers,
​

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

[MiguelCarrillo]

You could try using OLEDB VBO in order to perfom query to the Database.
First you need to download de OLEDB Provider for Oracle Database. 
https://www.oracle.com/mx/database/technologies/provider-ole-db.html

Then, Here is the connection string, you must set in the Aciton of the OLEDB VBO
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

If you have any doubts feel free to contact me.

------------------------------
Miguel Carrillo
------------------------------

[sonuiiml]

Thanks @ewilson

I was able to extract and establish the connection . Appreciate your support. 

However, while running SQL queries it gives the below error 
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Any thoughts?

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

[sonuiiml]

Thanks Miguel

I did tried with this but apparently ODAC needs to be installed. I am looking for some way to fetch data without installing any additional piece of software ​

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

[sonuiiml]

Hi @ewilson

This thing worked today. I tweaked the port ID( the Port ID I got from ORACLE SQL Developer) and it was all good to go. Thanks for all the brainstorming . Appreciate your support

------------------------------
Susamay Halder Consultant
Consultant
Bruce Power
+1(437)217-1086
------------------------------

[SADITYAKAPOOR]

Hi Eric,

I imported the VBO and Oracle DLL. but error on IsOpen page, where its check for connection object.

Error - Internal : Could not execute code stage because exception thrown by code stage: Could not load file or assembly 'Oracle.ManagedDataAccess, Version=4.122.19.1, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.

Please suggest.

Thank you

------------------------------
SADITYA KAPOOR
Senior Consultant
Capgemini Canada
America/Toronto
------------------------------

[ewilson]

Hi @SADITYA KAPOOR,

My guess is that if you've downloaded the Oracle MDAC library and installed it the likely issue is that Blue Prism can't locate the DLL on your system. Blue Prism expects that .NET assemblies will either be installed in the .NET GAC, the local Blue Prism installation folder (ie. C:\Program Files\Blue Prism Limited\Blue Prism Automate), or they must be referenced within the VBO with a fully qualified path.

If you take a look at the info block in the Initialise stage of the VBO​ you'll see that the DLL is referenced just by name. That means BP is looking in he GAC and the local BP directory. So, you have two options:

1. Copy the Oracle DLL into the Blue Prism Automate folder or install it in the GAC

or

2. Change the file reference in the VBO to include the full path to the DLL.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------