Oracle MDA Utility issue

[NupurSood]

Hello All 

Am using the Oracle MDA Utility VBO present on DX to execute my Oracle queries and save the results directly to a CSV. The problem is it works fine for the first query run but in case my second query run happens to be on a different connection string, the execution fails and I get the error - 'ORA-00942: table or view does not exist.

If I then go ahead and refresh my process and re-attempt the failed query run, it works just fine. I have a hunch that the previous connection doesn't get closed and the new connection isnt successfully established with the schema when I run the utility in a loop, even though closing any existing connections is a part of set connection action. 

Would really appreciate your guidance with respect to the same.

Best






<EDIT>

Thank you so much for the response  @GeoffHirst
It would be great if you could share the code changes required with me since I really want to use this VBO for one of my in development solution that is required to go live soon. The other Oracle VBO that Blue Prism natively provides isn't serving my requirement and use of this VBO was recommended by BP support themselves, so would really appreciate your help with the same.

​
Regards
Nupur

[GeoffHirst]

Hi Nupur,

Sorry you are having this issue. I created this asset over a year ago and you are my first real user. Now, firstly Oracle isn't my first database so please bear with me on this. I am going to review the asset bearing in mind what you have told me here. We may need to work on this to resolve the issue.

I will revert back to you with my findings and if I need any further help from you to resolve your issue.

<EDIT>

I have looked at the code for the Internal Close function. There is an issue that if you weren't clearing the pool (clearpool = true) then I would expect you to get the issue you have. However, this is still not correct functionality. I have made a change to the code to hopefully correct the issue. Now, I will be pushing an update to the DX with the changes I have made, however this may take a few days, if you need the change sooner, please let me know and I will issue you with instructions of what to do OR send you a new bprelease file.

Once again, apologies for the inconvenience.

</EDIT>

regards
Geoff Hirst

[GeoffHirst]

Hi Nupur,

I have updated the asset bprelease file now and it is available as OracleMDA_4.bprelease on github (Oracle MDA 4) . I hope this solves your issue, however please revert back to me if you are still having problems.

regards

Geoff Hirst

[NupurSood]

Hello @GeoffHirst

I downloaded OracleMDA_5 but am afraid I am still getting the same error.

Regards
Nupur​

[GeoffHirst · Accepted solution]

Hi Nupur,

Apologies, I think that we had a slight issue in that an old version was updated. I have now made 2 versions available, one for Blue Prism 6.5 and earlier and one for Blue Prism 6.6 and later. The issue you have, I am sure, is related to code in the Internal Close function. This, as I explained previously has been updated however the version of the file I placed up on github was replaced by the previous version being modified for the older versions of Blue Prism. Hopefully this is now resolved and you can continue your work.

Of course, if you still have issues, please come back to me.

regards
Geoff Hirst

[ParasPabari]

Hi @GeoffHirst, @Geoff Hirst, 

I downloaded the latest version for DX, "https://digitalexchange.blueprism.com/dx/entry/3439/solution/oracle-mda---utility" but I am still facing the same problem as mentioned above by @NupurSood. Is there a new version which I need to download and use. We use this VBO a lot and now after migrating to higher version of oracle we are using this to compare data between old and new but keeps on erroring out. 

[GeoffHirst]

hi @ParasPabari,

Thank you for being a SSC Blue Prism customer, and for using Blue Prism.

I am sorry to hear you are having issues with this asset. The issue Nupur had was resolved with the version that is currently live on the DX. So, we need to establish a few things so that I may assist you further.

1. What is your Oracle Version? - This is critical as the latest version is specifically for 21c. I have not tested it with Oracle beyond that version.
2. What is the version of the Oracle.ManagedDataAccess.dll that you have in your Blue Prism folder?
3. What version of Blue Prism are you working with?
4. Are you getting an ORA-???? error code of any kind?

If we can get the above questions answered I can see if I can help you resolve your issues.

Once again, thank you for using Blue Prism and for being a SSC Blue Prism customer.

regards

Geoff Hirst

[ParasPabari]

Hi @GeoffHirst, 

Please find the details below: 

1. What is your Oracle Version? - This is critical as the latest version is specifically for 21c. I have not tested it with Oracle beyond that version. (Ans: 19 C)
2. What is the version of the Oracle.ManagedDataAccess.dll that you have in your Blue Prism folder? (Ans: Version is 4.121.2.0 in BP folder) 
3. What version of Blue Prism are you working with? (Ans: BP version 7.1) 
4. Are you getting an ORA-???? error code of any kind? (Ans: getting two different errors for two different types of queries, error 1 in 1st database "Oracle error ORA-12571 encountered" and error 2 in another database is "ORA-00942: table or view does not exist" 

I was looking at the code and noticed that Internal Close function was having (Clearpool = false), is that intentional? I think if we make it as true it might resolve both issues. 

[GeoffHirst]

Hi @ParasPabari,

Do you have the 1.0.2 edition of the Oracle MDA asset from the Digital Exchange?

The reason I ask is, yes the clearpool test should be a true outcome, that code should be as shown here:

Please check the version of the asset that you have as I have looked on the DX this morning and checked the code there. This is the issue that Nupar had and the correction was made some time ago.

Please come back to me if you still have issues.

regards

Geoff Hirst