[Bug] table materialization fails when another MV with the same name exists in database

[push2prod]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

the table materialization for a table fails if there exists a Materialized view with the same name in a different schema.
the original table is not renamed to backup and the renaming of the new table fails on "an object with the same name already exists in the schema".
the problem is with the "oracle__list_relations_without_caching" macro
in https://github.com/oracle/dbt-oracle/blob/main/dbt/include/oracle/macros/adapters.sql.
it filters out tables which share a name with a materialized view in the whole database instead of just the relation schema.

Expected Behavior

it should be fixed to

{% macro oracle__list_relations_without_caching(schema_relation) %}
  {% call statement('list_relations_without_caching', fetch_result=True) -%}
    with tables as
      (select SYS_CONTEXT('userenv', 'DB_NAME') table_catalog,
         owner table_schema,
         table_name,
         case
           when iot_type = 'Y'
           then 'IOT'
           when temporary = 'Y'
           then 'TEMP'
           else 'BASE TABLE'
         end table_type
       from sys.all_tables
       where upper(table_name) not in (
          select upper(mview_name)
          from sys.all_mviews
          where upper(owner) = upper('{{ schema_relation.schema }}')
)
       union all
       select SYS_CONTEXT('userenv', 'DB_NAME'),
         owner,
         view_name,
         'VIEW'
       from sys.all_views
       union all
       select SYS_CONTEXT('userenv', 'DB_NAME'),
        owner,
        mview_name,
        'MATERIALIZED VIEW'
        from sys.all_mviews
  )
  select table_catalog as "database_name"
    ,table_name as "name"
    ,table_schema as "schema_name"
    ,case table_type
      when 'BASE TABLE' then 'table'
      when 'VIEW' then 'view'
      when 'MATERIALIZED VIEW' then 'materialized_view'
    end as "kind"
  from tables
  where table_type in ('BASE TABLE', 'VIEW', 'MATERIALIZED VIEW')
    and upper(table_schema) = upper('{{ schema_relation.schema }}')
  {% endcall %}
  {{ return(load_result('list_relations_without_caching').table) }}
{% endmacro %}

Steps To Reproduce

No response

Relevant log output using --debug flag enabled

No response

Environment

- OS: Debian Gnu/Linux 11 (bullseye)
- Python: 3.9.16
- dbt: 1.6, 1.7

What Oracle database version are you using dbt with?

19c

Additional Context

No response

[aosingh]

Ah, i remember adding this filter for a reason. Let me try to reproduce it.

[push2prod]

i think its because materialized views are always created as an MV and table, probably when you added the support for mv you didnt want the mv to show up as a table(maybe because of the special drop statement needed to drop them?), so the filter was added, i guess it was an overlook to not filter only MVs in the schema the relation should be materialized.

[aosingh]

Hi @push2prod

Thank you for reporting the issue and also suggesting the fix. I have included the fix in the next release candidate version

pip install dbt-oracle==1.7.2rc1

Could you please test with this and let me know if it works ?

[push2prod]

I checked the fixed version on the macro independently and it works great, but for now can't test version 1.7 as we are on version 1.6 because of cluster issues. But seems good to me!
Would welcome a 1.6 release as well

[aosingh]

dbt-oracle==1.7.2 is released with the fix.

We are also working on a backport for 1.6. I will let you know once it is released