Inaccurate error message when connecting to an Oracle DB from Alteryx

[TDGuy]

Not sure where else to provide feedback, but when I try and run a query with over 1000 criteria, I get an error in brio to that effect.  When I run it in alteryx, the error I get is a much more generic "DATAWrap OCI: Unable to prepare the query: ..."

I'm using version 10.6 because I need the 32bit client.

Would there be someplace with information where I can troubleshoot these errors better?

[Aguisande · Accepted solution]

Hi @TDGuy

I had this kind of errors in Oracle, when I try to Update / Insert new data into a DB, but one (or more) of the fields I prepare are different type/size of the original database or the destination database is empty.

I don't know if this is the case, maybe you can share a little bit more of the use case, so we can help.

Note : At the end of the error message (the one in red on the Result console), you can find the problem on the Database. Since this is a pass-through error (Alteryx just shows it), maybe you can copy the whole error and paste it in a text editor to access the last message, where usually is the explanation.

[SeanAdams]

The other thing you can do is take the query that you're running, and try to run that query directly on the oracle database using a query tool like Aqua Data Studio.

If you're getting this error from an in-DB query, you can get the actual query by using the dynamic outout in-DB (http://tinyurl.com/lv4kq6j)

What you may find is that you're getting an error which the Oracle server is not reporting correctly (I get this a lot with SQL errors on MSSQL)

if the query fails in a straight query tool, then you can progressively remove pieces until you get to the part that's actually failing.   If the query succeeds in the query window, then the error lies in the connection between Alteryx and your database.  You can again test this by trying to run the same query through an ODBC connection through MS Access (since access can also do pass-through query to ODBC).   

Hope this helps you to find the source of the error - if it works, would you mind posting your final finding, and marking as solved - but if not, then the next step would be to add on some detail about the table definition and the query you're trying to run so that we can get you to a solution.

Cheers

Sean

[TDGuy]

Thanks for the feedback, I've had similar issues, but this is not it in this case.

[TDGuy]

Thanks for the troubleshooting suggestions,  in this instance when I run the query in brio, the correct error message appears (over 1000 criteria limit reached).  When I run the same query in Alteryx, I get the generic error message as posted above. 

My concern is that I would like to be able to evaluate the issue within Alteryx because I don't always have the ability to cross check with a query that is known to be functional in both systems. 

[TDGuy]

---

@Aguisande wrote:

Note : At the end of the error message (the one in red on the Result console), you can find the problem on the Database. Since this is a pass-through error (Alteryx just shows it), maybe you can copy the whole error and paste it in a text editor to access the last message, where usually is the explanation.

---

Pasting into a text editor enabled me to view the actual error message.  Thanks.