In-DB Error: Error while trying to retrieve text for error ORA-28547

[john_watkins]

I have a workflow that uses in-db to connect to two different oracle databases.    It was working last night and then decided to stop working today.  The only thing I did was I had cached the results of the first in-db and then added the other connection.   Today it was all working so I turned off the cache and I get this error.

I am on Alteryx 2020.3.4.   I see one posting saying to check an environment variable and did that with no luck.    Please tell me that Alteryx does have issues pulling from more than one database.   I also switched over to using the gallery connection with a basic input tool and that also returns the same error.

Error while trying to retrieve text for error ORA-28547

[BrandonB]

https://help.alteryx.com/20213/designer/oracle 

"A single workflow with ODBC connections cannot contain either OleDB or OCI connections using the same driver."

Maybe this is the issue? What connection types are you using? 

[john_watkins]

OCI - Oracle (DB-A; x2)  in-db

OCI - Oracle (DB-B; x1)  in-db

SNOWBI - SnowFlake (DB-C; x1) in-db

SNOWBI - SnowFlake (DB-C; x1) stream out via in-db

[BrandonB · Accepted solution]

Just as a test, can you try using the ODBC setting for your Oracle connections? 

[john_watkins]

I did see that I was making two different Oracle databases using the same drivers.   I am surprised this isn't handled to be honest when the "get data from anywhere" get thrown around with this tool.  I have temporarily resolved this by turning off a portion at a time and caching and then re-running until I can change something up.  

@BrandonB-  I am using connection files and haven't tried to change them or created a new copy and modified it.  I may be able to create a user connection, but that will be the same amount of work to do/undo as my work around above.  I can't leave it like that for production. 

[john_watkins]

@BrandonB - I was wrong.  I re-read your last comment and still used my gallery setting and changed the source type from OCI to ODBC and it did work.   It would appear that work around will do for when there are TWO concurrent connections to multiple databases of the same type.   Thanks!!

[BrandonB]

Sorry I could have made it more clear in my explanation. Yep, Oracle is just a bit picky about multiple connections using the same driver. Glad it is working for you now! 

[john_watkins]

Unfortunately, something must have been cached still when testing as my run resulted in the same error.   I am resorting to copying a table to a new database at the moment as I need this done ASAP.  I have an open ticket with support and am still waiting to hear from someone.

[PatPli]

Hello,

I know this post is a bit old but I'm facing a similar problem on the Alteryx server (when a workflow contains 2 target tables with the same ODBC then only one of the 2 is loaded and for the 2nd I have the message ORA -2857). No problem on the designer on my computer or on the Alteryx server, both tables load correctly.

@john_watkins, did you have an answer from the support and manage to solve this problem ?

Detail of my problem (in french) : Driver Simba Oracle et erreur ORA-28547 quand char... - Alteryx Community

Thanks. 

[PatPli]

Forget my previous post, I found the solution : installation of a complete Oracle Client (https://www.oracle.com/database/technologies/oracle19c-windows-downloads.html > WINDOWS.X64_193000_client_home.zip) instead of only an instantclient Oracle + Alteryx server reboot and now it's OK.