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

[ChrisNewsom]

Have you tried amended the date format? We have experienced issues before as Blue Prism formats dates differently to SQL Developer.​

------------------------------
Christopher Newsom
RPA Developer
Leeds Building Society
Europe/London
------------------------------

[JonathanHolstin]

Hi Christopher,
I'll give that a shot. Wouldn't that date problem cause the collection to end in exception?

Thanks,
Jon

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

[JonathanHolstin]

Hi Christopher,
I originally just changed from passing a variable for date to a hard coded '01-MAR-2020'.  It turns out after a bunch more trouble shooting it rolled back to the date.  Any chance you know what the issue/resolution was on your end for dates?  

Thanks!
Jon

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

[GopalBhaire]

Can you write this as store procedure and then run from BP?
Was BP support of any help?

------------------------------
Gopal Bhaire
Analyst
Accenture
------------------------------

[KeithWoof]

Hi Jonathan,
Have you got this resolved?
If not try updating the SQL to derive the date from SYSDATE.
Alternatively we use a calculation to replace date parameters in the SQL and format the date to this format 'yyyy-MM-dd HH24:MI:SS'

------------------------------
Keith Woof
RPA Manager
Leeds Building Society
Europe/London
------------------------------

[AndreyKudinov]

Try removing comments "--VNickols added to accommodate new MCC LOA" etc, if something is wrong with newlines, that might screw things, but just a wild guess.

In general, if you use multiline queries, store them(parts) in text dataitems, dont just set input to something like:
"select *
from tbl
where xx="&[myvar]
This often looses newlines..
------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------