[Bug] Oracle error: ORA-00904: "COLUMN": invalid ID

[kriced-netcologne]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

I added a new column to my staging model ("TEST") and ran my downstream snapshot model with explicit check strategy - not including the new "TEST" column.
During my snapshot run, the adapter tries to call default__create_columns() which fails for Oracle syntax.
This is due to the "column" keyword and the trailing semicolon in the invoked statement.

Proposed fix (also works temporarily by overwriting with dispatch):

{% macro oracle__create_columns(relation, columns) %}
  {% for column in columns %}
    {% call statement() %}
      alter table {{ relation }} add "{{ column.name }}" {{ column.data_type }}
    {% endcall %}
  {% endfor %}
{% endmacro %}

Expected Behavior

1. Alter table, add new column to snapshot table
2. Apply snapshot strategy

Steps To Reproduce

No response

Relevant log output using --debug flag enabled

Environment

- OS: Windows
- Python: 3.11.9
- dbt: 1.10.13

What Oracle database version are you using dbt with?

19

Additional Context

No response

[aosingh]

@kriced-netcologne

Thanks for pointing out the issue and the solution. I have included this in our upcoming release of dbt-oracle.

Could you please test with the release candidate version and let me know if it works ?

pip install dbt-oracle==1.10.0rc1

You might have to remove the project local macro