Oracle SQL_Data - Oracle (MDA)

[faheemsd]

Hi Team,

I'm using Data - Oracle (MDA) VBA to execute the one of the Query to alter the package but it's giving the error like Operation is not valid due to the current state of the object. and I'm using the Execute Action from the VBO.
My Query looks like this for example
alter package ABCD_XYZ_ODI_PROCESS compile body

if I run the same query from the Oracle SQL it's giving the result for me


Could you please help on this?

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

[GeoffHirst]

HI Syed,

Thank you for being a SSC Blue Prism Customer and for using Blue Prism. I am sorry you are experiencing issues with the Oracle VBO.

Can we establish some initial setup parameters?

1. Are you using Oracle 21c?
2. In your process, have you set the connection parameters to connect to your Oracle system?
3. Have you tried executing a very simple SQL statement to see if it returns correctly?
4. Do you have a transaction set up for this action?

If you can come back to me with answers to the above I will see what we can resolve for you.

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

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------

[faheemsd]

Hi Geoff,

Thank you for your response.
1. Are you using Oracle 21c? 
We are using the Oracle SQL Developer 

2. In your process, have you set the connection parameters to connect to your Oracle system?
Yes we have set the connection parameters and it's working perfectly

3. Have you tried executing a very simple SQL statement to see if it returns correctly?
We are already using this for Select Query and it is working perfectly

4. Do you have a transaction set up for this action?
Yes, We have all the Transaction Set up in the oracle SQL Developer and if I run the Query in the Oracle SQL Developer i's giving the results and if I run the same query with Blue Prism I'm getting the error as I mentioned in my post.

Could you please help us here?

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

[GeoffHirst]

Hi Syed,

Thank you for the above. 

SQL Developer is your client in this case, what is the version of Oracle that you are connecting to?

Could you show an image of your process that you are using so that I may see its component parts? I need to establish how the execute is being called and what is different about the SQL you are sending. In the meantime, I am configuring an Oracle VM here to try to replicate your problem. Once I am able to replicate it, we can go about finding you a solution.

regards

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------

[GeoffHirst]

Hi Syed,

I have been working on your problem and I have discovered an issue with the asset, in particular the execute. I have made some additions to the asset and updated the example process. I will be updating the documentation soon and then I will post this update to the Digital Exchange. Once it has cleared QA it will be available for download. I expect this to be in the next few days, possibly early next week. 

Sorry for the inconvenience.

regards

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------

[faheemsd]

Hi Geoff 

Thank you for the Update.

Could you please let me know once it's available in Digital Exchange?

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

[GeoffHirst · Accepted solution]

Syed,

As requested, the Oracle MDA asset has now been updated on the DX. Please could you feedback your experience to this thread with the new version. I did test it with similar activity to what you were trying to undertake and it performed satisfactorily in my case.

regards

------------------------------
Geoff Hirst
Senior DX Engineer - Digital Exchange - EMEA
SS&C Blue Prism
------------------------------

[faheemsd]

Dear @Geoff Hirst 

Appreciate your efforts for resolving the issue with the Execute function.

After using the latest version of Oracle MDA , I'm getting the result as success after executing the Query with the Execute functionality.

Thank you very much.

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

[zdenek.kabatek]

Hello, Geoff,

thanks for the asset. I could successfully connect to Oracle database I can run the query, however, I am not getting the data in CSV when calling that particular action neither collection. When I run simple query "select * from person_info" which returns two records from Oracle (in SQL Developer) and I run the same query in the process I am getting following results:

1. action Get Collection - Success True, Result Collection Empty, Message empty
2. action Get CSV File - I get the Success True, Message empty and I get CSV file but only the column names no data and there should be two records
3. action Get CSV 0 Success True, Message empty and CSV data item contains only headers and then two empty lines

Do you know what the reason for this could be? I am using Oracle 21c.

Thanks

Regards

Zdenek
------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

[zdenek.kabatek]

Hi, Geoff,

I take my question back as I did beginners mistake. Even though I could see all the records created in SQL Developer (I created the tables) it was not committed until I closed the session. Therefore after committing the changes everything worked perfectly fine!

Thanks.

Regards

Zdenek

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------