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

[ASMR]

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?

[JagdeeshN]

Hi @ASMR ,

With the same example of a Pre-SQL to Drop the table, can you change the option to create a new table instead of overwrite.

Do let me know if that helps.

Best,

Jagdeesh

[TomWelgemoed]

Hi @ASMR ,

Interesting. Is it possible that you're referring to the same database table twice in the same workflow, i.e. that the workflow inadvertently does this twice? Then it gets a conflict when either trying to create or select?

[ASMR]

Thanks for the reply. I thought the same thing and tried that after posting, but same results. 

[ASMR]

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?

[SeanAdams]

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-... 

[aAK2K22]

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?