Alteryx and Oracle - Internal Error in Opening SQL Statement

Question

When output options are set to any of the updates, I get "Internal Error in opening sql statement: <missing argument Text 1>.  If I set the option to delete data and append, it works just fine and populates the table.  I think it has something to do with the field mapping, in the drop down for the key field in the destination, it has field name (key) , but the column next to it with the heading of KEY does not show anything.  The underlying data type on the key column is RAW and stores a GUID.

I just created a new table and use nvarchar to hold the guid string, same error.   Seems like Oracle and alteryx not able to read schema info or something for the update.

chris_oneslager · Answer

Hi Treyson,

All the different oracle drivers dont like to read the raw type, the message comes up saying it is not supported and the value will be null and it shows as a binary data type.  I dont know why the developers selected raw as the datatype, but we are storing a guild(string) in it.   To be able to read it into alteryx, I had to wrap it in a rawtohex() oracle function bring it into alteryx as a string.  From there, I was not able to save it back to a destination (different db, same schema) until I tried the OCI driver for oracle.  I tried everything except ODBC and all had similar errors with the missing parameter like in the other message.  It appears the OCI driver is able to write back to the db fine, taking the string from alteryx and storing into a raw field.  This is where I ended up yesterday and continuing to test.

Treyson · Answer

Hey @chris_oneslager

I would love to help, but I need some clarification on what you are asking. When you say " output options are set to any of the updates". What does that mean specifically? Are you saying "Update; Insert if New"?

I did some reading into this Oracle "Raw" DataType. What is the DataType of your field currently? It looks like some other Alteryx users have found success making this field VW_String and then making sure it's long enough. I imagine if it's some sort of numeric field like int64 Oracle straight up won't do the comparison on the key?

Let's explore this, because I want to know too.