OLEDB - Oracle - Not pulling data - SQL Dev pull works

[JonathanHolstin]

Hello,
I am running into an issue I haven't experienced before. I have an OLEDB connection where I am running three different sql statements through getting collections. One of these is not pulling any data. If I copy the statement over to my sql developer and run it, it pulls data.  It isn't causing any errors or bombing out, just not pulling any data.  

Does anyone have any ideas regarding what may be the cause or has anyone ran into this issue before?

Thanks,

Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

[IanCampbell]

Hi Jonathan,

I have a few questions that may help other people in getting you some sort of solution:
1) Is the query that returns no data overly complex in the logic?
2) Have you tried to simplify the query and test individual chunks of logic to check they are returning the expected results? (e.g. run just the SELECT and FROM portions of your query and check this returns as expected then incrementally add in more of the filtering/joining etc)
3) Have you checked that the connection string is connecting to the desired data source?

The other thing I would be weary of is directly copying queries in to the Blue Prism OLEDB capability as you will need to check that the functions you are using are supported by OLEDB. ​I've found it can sometimes be better to build queries in Blue Prism and continually test the logic against the expected output. You can also view text in SQL format if you go in to a text data item, press the ellipsis button to open the text box and then select SQL from the dropdown menu which will help with formatting issues during query creation.

Thanks,

Ian

------------------------------
Ian Campbell
RPA Specialist
Allen & Overy
------------------------------

[JonathanHolstin]

Hi Ian,
Thanks for your response and questions.

1)  It is a bit of a complex query. I've used a few different complex queries and haven't had any issues with them before.
2)  I've tried to do portions but when I chop out sections it actually causes the sql to run much slower in sql developer. In BP this actually gets a system error "Could not execute code stage because exception thrown by code stage: Exception of type 'System.OutOfMemoryException' was thrown."  I am going to try and keep chopping it but not sure this method will work.
3) The connection string works. I have three different collections/sql statements being ran within the connection. The first two gather collections and this one runs, doesn't error out, appears to complete..........but doesn't actually get any data.

I'm passing the sql statement into a data item via a calculation and then using the data item for the Value of the collection. This has worked in many other places and it doesn't seem to be the issue this time.

Any other ideas?

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

[VivekGoel]

May be bit naive, but did you check if by mistake you have a semicolon at the end of the statement in the "dataitem" where the SQL is stored?

------------------------------
Vivek Goel
"If you like this post, please press the "Recommend" Button.
------------------------------