[Bug] temporary table not dropped when error is raised

[fabrcav]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

if an error occurs during an incremental model execution the temporary table O$PT_ is not dropped

Expected Behavior

if an error occurs all temporary tables are dropped.

Steps To Reproduce

1. create model materialized='incremental'
2. put an inconsistency in the model

Relevant log output using --debug flag enabled

...
Database Error in model target_table (models/target_table.sql)
  ORA-01400: cannot insert NULL into ("ADMIN"."TARGET_TABLE"."C2")
  Help: https://docs.oracle.com/error-help/db/ora-01400/
  compiled code at target/run/dbt_error_example/models/target_table.sql
15:41:53  Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '89902985-b71a-4396-9275-cf076bfb142f', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7126987acc10>]}
15:41:53  1 of 1 ERROR creating sql incremental model admin.target_table ................. [ERROR in 0.46s]
15:41:53  Finished running node model.dbt_error_example.target_table
15:41:53  Marking all children of 'model.dbt_error_example.target_table' to be skipped because of status 'error'.  Reason: Database Error in model target_table (models/target_table.sql)
  ORA-01400: cannot insert NULL into ("ADMIN"."TARGET_TABLE"."C2")
  Help: https://docs.oracle.com/error-help/db/ora-01400/

Environment

- OS: Ubuntu 22.04.5 LTS
- Python: 3.10.16
- dbt: 1.9.3
  - dbt-oracle: 1.9.0

What Oracle database version are you using dbt with?

19.0.0.0.0

Additional Context

the search for the bug lead me to this other closed bug that seems very similar (if not the same)
i prepared an example repo were you can test the exact case if you need to

[aosingh]

Hi @fabrcav

I am checking this.

Ideally, I would have liked some try..finally block equivalent in dbt macros which I have not found

[fabrcav]

Hello @aosingh,
thank you for loking into this. seems like a package that implements try...finally blocks for jinja exists maybe this can be integrated?