What’s the secret to getting Knime and Oracle to play nice with global temporary tables? Every time I create them in Knime if the session gets reset and even closed out and reopened the temp tables persist as if they were regular tables. Simply resetting the connection does not drop the session. I’m using the following syntax which builds them fine, but if the connection is reset it requires dropping them explicitly forcing the need for some kind of loop to be introduced to confirm if they exist instead of just dropping them when session resets.
create global temporary table ATableName
ON COMMIT PRESERVE ROWS
as
select * from tablename
;
commit
;