It seems that some nodes (e.g. DB Writer) are quoting table names and confusing Oracle because table_name <> “table_name”. Oracle treats them as separate objects and it does not list them in all_objects as having quotes when a node uses them. This created some confusion initially because my table builds would complete and then become ghosts to other nodes until I took a closer look at how nodes are creating them.
I switched to using DB SQL Executor to build tables so I have control over the DDL statement, but then it appears to get confused if the schema is not uppercase, which isn’t an issue with the sql client I’m testing everything with. Once it builds the table I can describe it with the new table name, but DB Table Remover can’t seem to remove it. It executes fine, but the table is never removed. That node would be markedly improved if it was possible to see the code it’s attempting to execute, but it’s a black box making it difficult to troubleshoot.
So at the moment I’m trying to establish the best approach to testing for the presence of a table and removing it, which I probably can do by running a query against user_tables to see if it exists, but felt these nodes were designed to reduce the number of steps involved. I don’t know if this behavior is specific to Oracle, but it seems like something that should be part of a future release.
It appears DB Table Remover can only read names in upper case even if they were named in lower case. So it might be useful to include some mention of the case-sensitive nature or update the node to change case underneath the covers.
The other wrinkle is that DB Writer can’t seem to figure out the data types that DB SQL Executor has created. A table was built, it was described to confirm the table was built correctly, and then DB Writer returns data type mismatch errors as though it’s looking at another table, but it’s getting the schema and table from a flow variable, so it’s looking at the correct object with the correct uppercase name. This just seems unnecessarily complicated.