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?
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
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?
Thanks for the reply. I thought the same thing and tried that after posting, but same results.
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?
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:
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?