topic RE: Getting Data from Oracle Database in Product Forum

Getting Data from Oracle Database

sonuiiml — Mon, 04 Jan 2021 18:51:00 GMT

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

RE: Getting Data from Oracle Database

ewilson — Mon, 04 Jan 2021 19:17:00 GMT

Hi @Susamay Halder,

I believe for Oracle you will need to have their Data Access components installed. There's actually an Oracle integration available on the DX, and the documentation discusses the necessary requirements. You can find it here: https://digitalexchange.blueprism.com/dx/entry/9648/solution/blue-prism---oracle-mda---utility-2

Cheers,

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

RE: Getting Data from Oracle Database

sonuiiml — Mon, 04 Jan 2021 20:39:00 GMT

Thanks Chris.

I tried importing the bprelease file however its gives the below error

Any thoughts?

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

RE: Getting Data from Oracle Database

ewilson — Mon, 04 Jan 2021 20:48:00 GMT

What version of Blue Prism are you running?

Cheers,
Eric

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

RE: Getting Data from Oracle Database

sonuiiml — Mon, 04 Jan 2021 20:55:00 GMT

I am using blue prism version 6. Is that impeding the extraction process?

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

RE: Getting Data from Oracle Database

ewilson — Mon, 04 Jan 2021 21:08:00 GMT

That could be the issue. I imagine this release was created in 6.8 or 6.9. The makeup of the bprelease file changed a bit in 6.6.

Let me work a little magic on the file shortly, and then update it on GitHub. I'll let you know as soon as it's ready.

Cheers,

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

RE: Getting Data from Oracle Database

ewilson — Mon, 04 Jan 2021 21:41:00 GMT

@Susamay Halder,

There's a new version of the .bprelease file available on the GitHub page. This should work for you.

Cheers,

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

RE: Getting Data from Oracle Database

sonuiiml — Tue, 05 Jan 2021 13:32:00 GMT

@Charles

I did tried with the new release file you have shared on github page but the error remains.

Do I need to put the dll files in blue prism automate folder and then give it a try?

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

RE: Getting Data from Oracle Database

ewilson — Tue, 05 Jan 2021 13:47:00 GMT

Try opening the .bprelease file with a text editor (ex. Notepad). Do you see HTML tags in the file or just XML?

Cheers,

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

RE: Getting Data from Oracle Database

sonuiiml — Tue, 05 Jan 2021 14:03:00 GMT

@ewilson

Hi Eric,

I did tried saving the file in notepad. Do you mind taking a look? it there in attachment.
Thanks

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

RE: Getting Data from Oracle Database

ewilson — Tue, 05 Jan 2021 14:40:00 GMT

@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=digestviewer

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

RE: Getting Data from Oracle Database

sonuiiml — Tue, 05 Jan 2021 15:46:00 GMT

@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
------------------------------

RE: Getting Data from Oracle Database

ewilson — Tue, 05 Jan 2021 20:27:00 GMT

@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
------------------------------

RE: Getting Data from Oracle Database

MiguelCarrillo — Wed, 06 Jan 2021 00:02:00 GMT

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

RE: Getting Data from Oracle Database

sonuiiml — Wed, 06 Jan 2021 21:28:00 GMT

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

RE: Getting Data from Oracle Database

sonuiiml — Wed, 06 Jan 2021 21:30:00 GMT

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

RE: Getting Data from Oracle Database

sonuiiml — Mon, 11 Jan 2021 15:18:00 GMT

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

RE: Getting Data from Oracle Database

SADITYAKAPOOR — Tue, 12 Jan 2021 02:03:00 GMT

RE: Getting Data from Oracle Database

SADITYAKAPOOR — Tue, 12 Jan 2021 14:21:00 GMT

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

RE: Getting Data from Oracle Database

ewilson — Tue, 12 Jan 2021 15:15:00 GMT

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