[Bug] don't drop temporary table o$pt

[AlexMorti]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

The global temporary table is not deleted after running the model due to an

[dbt-oracle/blob/main/dbt/include/oracle/macros/adapters.sql](

dbt-oracle/dbt/include/oracle/macros/adapters.sql

Line 259 in 7348c2b

pragma EXCEPTION_INIT(attempted_ddl_on_in_use_GTT, -14452);

)

example:
create global temporary table TEST (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Such tables get bound to the session with the first insert and then this binding is ONLY removed when the session exits or by truncate. If the binding exists DDL cannot be performed on these temporary tables and if you try a DDL on such tables you would receive ORA-14452 attempt to create, alter or drop an index on temporary table already in use.

add before

dbt-oracle/dbt/include/oracle/macros/materializations/incremental/incremental.sql

Line 63 in 7348c2b

{% for rel in to_drop %}
{% do adapter.truncate_relation(rel) %}
{% endfor %}

Expected Behavior

drop table

Steps To Reproduce

1. create model materialized='incremental'

Relevant log output using --debug flag enabled

No response

Environment

- OS:MacOs
- Python:3.9
- dbt-core: 1.1.0

What Oracle database version are you using dbt with?

12.1.0

Additional Context

No response

[aosingh]

Hi @AlexMorti

Thank you for the detailed bug report. I would like to understand how to reproduce the error ORA-14452

In dbt-oracle, temporary tables are named in the following format:

    {% set dtstring = dt.strftime("%H%M%S") %}
    {% set tmp_identifier = 'o$pt_' ~ base_relation.identifier ~ dtstring %}

So, unless you have multiple dbt invocations at the same time (precisely the same second), you wouldn't get ORA-14452

[AlexMorti]

test model
{{config(materialized='incremental')}} select 1 as user_id, 'test' first_name, 'test2' last_name from dual

if you remove the exception condition

dbt-oracle/dbt/include/oracle/macros/adapters.sql

Lines 262 to 263 in 461f58c

	WHEN attempted_ddl_on_in_use_GTT THEN
	NULL; -- if it its a global temporary table, leave it alone.

then there will always be an error:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
logs attached
dbt.log

[ThoSap]

Current Behavior

The global temporary table is not deleted after running the model due to an

Hi @AlexMorti, you did not finish this sentence regarding the current behavior.
Are you getting an error, if yes which one?

I had the same issue that the temporary table was not dropped caused by the following issue #8 as the incremental materialization failed due to the ORA errors mentioned in the ticket.

[aosingh]

@AlexMorti

Thanks for attaching the logs. I am checking this

[aosingh]

@AlexMorti

I checked the logs. It is not clear to me why do you want to delete the line. What is the use case ?

WHEN attempted_ddl_on_in_use_GTT THEN 
    NULL; -- if it its a global temporary table, leave it alone. 

If you keep this line, what is the error you get when you run incremental materialization ?

[aosingh]

Hi @AlexMorti

Could you run dbt without any code changes for an incremental model and share the log ? I would like to understand the ORA errors you get without any code changes.

Thank you

[aosingh]

Hi @AlexMorti

I could reproduce the issue and confirm that O$PT tables are only truncated at the end of the dbt session but not dropped. This will be fixed using a TRUNCATE statement before DROPPING the table as you proposed.

Thank you.