"Table does not exist" & "name is already used by an existing object" in Output Tool

Question

I am writing my data to an Oracle ODBC and running a Post-SQL command and am stumped by the errors I'm getting for it. I initially set the Output Option to be "Overwrite Table (Drop)", but that was giving me a "Unable to prepare the Create Table Statement...Error: ORA-00955: name is already used by an existing object" message. This confounded me, since I was just trying to overwrite the table rather than create a new one. Other Community users suggested adding a "DROP TABLE" command in the Pre Create SQL Statement, which is what I've done in my current configuration:

However, running the workflow on either the server or on my machine yields the following errors:

How can it be that I am simultaneously getting a "table or view does not exist" error AND a "name is already used by an existing object" error? And why does it keep trying to create a table when I have set it to overwrite it?

aAK2K22 · Answer

I am connecting to a different database (Teradata) and am running a select query and based on the fact that the table does not exist am using the CREATE NEW TABLE option in the Output Data followed by a Post Create SQL Statement to GRANT permission to required users on the said data. The connection type is a 64 bit ODBC with the table name specified during the database connection selection. It still gives me the error on the GRANT statement. When I checked whether the table was created, it did get created. I dropped that table and reran the workflow with the same results. Is it something to do with TRANSACTIONs  and the way they are committed?

SeanAdams · Answer

Hey @ASMR ,

My guess is that the reason you're getting these two errors is that you're getting an error on the drop which is not being reported.

There are a few things it could be:

* The login that you're using may not have Drop permission
* Oracle is very case sensitive - you can test the drop statement in a Oracle query tool to see if this works
* You could try changing to an OCI connection rather than an ODBC connection to see if that works: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/What-is-the-difference-between-an-oci-and-an-odbc-connection/td-p/723506

ASMR · Answer

I don't think so, because this output tool (which is at the very end of my workflow) is the only instance where this table is mentioned. Additionally, I find it interesting that that the error for the Post SQL comes before the error for creating the table. I wonder if that means anything?