RunQuery activity, got empty datatable. How can I check for Oracle error codes if any?

I have a list of queries that run over the same Oracle Host and SID (I mean, using same conection). When iterating that list of queries and using Run Query activity, some of those querys retrieve the expected data in the DataTable, but others returns just 0 records:

imagen

It’s strange that If I convert the datatable to string with OutputDatatable activity, then I’m getting newline: “/r/n”. :face_with_diagonal_mouth:

But, if I copy* that very same query which is returning empty, and then run it with Oracle client (i.e. SQLDeveloper desktop), then it returns plenty of records

* query copied from a log activity, so yes, it is the very same query that ran in UiPath.

So… whats the problem? Is there a way of debuging what the Run Query activity does inside? how can I get oracle error codes or warnings when running querys by means of the Run Query activity.

Any help is welcome. Thank you guys.

@amozoh

Welcome to forums

Can you give some delay to execute and return the results?

Thanks,
Srini

Srini, thank you very much.

Tried adding delays (6 secs between consecutive queries). Now I’m getting error in Run Query activity:

imagen

The query before was ok, but then, the next query is giving this error.
You can see the query uses a date CAST that can be problematic… but then again, the query before uses the same condition and works.

@amozoh

Okay, there was some Date CAST issue where you have to handle that correctly?
Previously this was not an error because there was no delay and I belive we didn’t give enough time to get back the results

Hope this may help you

Thanks,
Srini

Hi @amozoh ,
Thanks for reaching out to UiPath Community.

Check that the query syntax used in UiPath’s Run Query activity is exactly the same as the one used in Oracle client. Even a small difference in the syntax can result in different results. You can also try running the query directly in Oracle using the SQLPlus command-line interface, as this will give you more detailed error messages if there are any syntax errors.

Regards,
@pratik.maskar

Hi pratik.maskar

I guess the syntax of the query that I run on client is the same as the query that I run with UiPath, as I’m getting the query from Log Activity.

I’m using a previous RunNonQuery activity in order to perform an alter session so the date format displays in a certain way.

imagen

But this is done for all the queries that I iterate through the list, and many of them use TO_DATE() in the where clause and excute right, returning data, not error. Im not sure is this is an UiPath issue or an Oracle/SQL issue with mi queries.

Thank you for your answer, thought

EDIT: apparently, the alter nls_date_format is not working. The previous queries somehow execute fine, but the date fields are not showing date in the expected format. So I guess it is a problem of being unable to get UiPath to execute the alter session and que select query in the same sessión… I guess I must alter the querys to explicitly transform the date fields returned and the date comparisions in the where clause…

Hello again Srinivas,

Finally found the issue. Two of the retrieved fields in the select part of the query, do have a large decimal part, and that caused the issue. I solved it by truncating the field: TRUNC(long_tricky_number,2).

So it wasn’t related with the TO_DATE call in the where clause.

Anyway, not sure why it works from Oracle client. I think UiPath has problems when trying to copy/map the query output to DataTable object with large decimal fields (that is done in the Run Query activity, along with the execution of the query).

Now I have another issue with other query; will probably open a new case.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.