Repository: oracle/dbt-oracle (language: python)

Issue:
[Bug] table materialization fails when another MV with the same name exists in database
### Is there an existing issue for this?

- [X] 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
```sql
{% 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

```markdown
- 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_


Discussion / hints:
Ah, i remember adding this filter for a reason. Let me try to reproduce it. 
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.

Fix patch:
diff --git a/Makefile b/Makefile
index 62696ff..b02c0a3 100644
--- a/Makefile
+++ b/Makefile
@@ -1,5 +1,5 @@
 # Configuration variables
-VERSION=1.7.1
+VERSION=1.7.2
 PROJ_DIR?=$(shell pwd)
 VENV_DIR?=${PROJ_DIR}/.bldenv
 BUILD_DIR=${PROJ_DIR}/build
diff --git a/dbt/adapters/oracle/__version__.py b/dbt/adapters/oracle/__version__.py
index 39b5e51..2f57bae 100644
--- a/dbt/adapters/oracle/__version__.py
+++ b/dbt/adapters/oracle/__version__.py
@@ -14,4 +14,4 @@
   See the License for the specific language governing permissions and
   limitations under the License.
 """
-version = "1.7.4"
+version = "1.7.7"
diff --git a/dbt/include/oracle/macros/adapters.sql b/dbt/include/oracle/macros/adapters.sql
index 2ac93f3..c1c2fb2 100644
--- a/dbt/include/oracle/macros/adapters.sql
+++ b/dbt/include/oracle/macros/adapters.sql
@@ -40,7 +40,11 @@
       {%- if col['data_type'] is not defined -%}
         {{ col_err.append(col['name']) }}
       {%- endif -%}
+      {%- if col['data_type'] | lower == 'clob' -%}
+      empty_clob() as {{ col['name'] }}{{ ", " if not loop.last }}
+      {%- else -%}
       cast(null as {{ col['data_type'] }}) as {{ col['name'] }}{{ ", " if not loop.last }}
+     {%- endif -%}
     {%- endfor -%}
     {# Override for Oracle #}
      from dual
@@ -268,7 +272,7 @@
       {% set comment = column_dict[column_name]['description'] %}
       {% set escaped_comment = oracle_escape_comment(comment) %}
       {% call statement('alter _column comment', fetch_result=False) -%}
-        comment on column {{ relation }}.{{ column_name }} is {{ escaped_comment }}
+        comment on column {{ relation }}.{{adapter.check_and_quote_identifier(column_name, model.columns)}} is {{ escaped_comment }}
       {%- endcall %}
     {% endfor %}
   {% endif %}
@@ -362,7 +366,11 @@
            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(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,
diff --git a/dbt/include/oracle/macros/catalog.sql b/dbt/include/oracle/macros/catalog.sql
index b68fff5..3adc9f4 100644
--- a/dbt/include/oracle/macros/catalog.sql
+++ b/dbt/include/oracle/macros/catalog.sql
@@ -27,7 +27,9 @@
                      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(table_name) not in (
+                 select upper(mview_name)
+                 from sys.all_mviews)
                  union all
                  select SYS_CONTEXT('userenv', 'DB_NAME'),
                    owner,
diff --git a/requirements.txt b/requirements.txt
index db63fd0..8f5539a 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -1,3 +1,3 @@
 dbt-core~=1.7,<1.8
 cx_Oracle==8.3.0
-oracledb==2.0.0
+oracledb==2.0.1
diff --git a/setup.cfg b/setup.cfg
index b7be6e5..31bcdea 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -1,6 +1,6 @@
 [metadata]
 name = dbt-oracle
-version = 1.7.1
+version = 1.7.2
 description = dbt (data build tool) adapter for Oracle Autonomous Database
 long_description = file: README.md
 long_description_content_type = text/markdown
@@ -34,7 +34,7 @@ include_package_data = True
 install_requires =
     dbt-core~=1.7,<1.8
     cx_Oracle==8.3.0
-    oracledb==2.0.0
+    oracledb==2.0.1
 test_suite=tests
 test_requires =
     dbt-tests-adapter~=1.7,<1.8
diff --git a/setup.py b/setup.py
index 9e09d27..aaa6d8b 100644
--- a/setup.py
+++ b/setup.py
@@ -42,7 +42,7 @@
 requirements = [
         "dbt-core~=1.7,<1.8",
         "cx_Oracle==8.3.0",
-        "oracledb==2.0.0"
+        "oracledb==2.0.1"
 ]
 
 test_requirements = [
@@ -60,7 +60,7 @@
 
 url = 'https://github.com/oracle/dbt-oracle'
 
-VERSION = '1.7.1'
+VERSION = '1.7.2'
 setup(
     author="Oracle",
     python_requires='>=3.8',


Test patch:
diff --git a/dbt_adbs_test_project/dbt_project.yml b/dbt_adbs_test_project/dbt_project.yml
index e73dc0a..4298fc7 100644
--- a/dbt_adbs_test_project/dbt_project.yml
+++ b/dbt_adbs_test_project/dbt_project.yml
@@ -18,6 +18,9 @@ quoting:
 seeds:
     dbt_adbs_test_project:
         quote_columns: false
+        kafka_message:
+          +column_types:
+            message: CLOB
         seed_with_empty_col:
           +column_types:
             id: number
diff --git a/dbt_adbs_test_project/models/kafka.sql b/dbt_adbs_test_project/models/kafka.sql
new file mode 100644
index 0000000..512e40e
--- /dev/null
+++ b/dbt_adbs_test_project/models/kafka.sql
@@ -0,0 +1,1 @@
+select * from {{ ref('kafka_message') }}
\ No newline at end of file
diff --git a/dbt_adbs_test_project/models/schema.yml b/dbt_adbs_test_project/models/schema.yml
index ba8c5df..9f3d26e 100644
--- a/dbt_adbs_test_project/models/schema.yml
+++ b/dbt_adbs_test_project/models/schema.yml
@@ -22,6 +22,26 @@ sources:
       - name: costs
 
 models:
+  - name: kafka
+    description: kafka_description
+    config:
+      materialized: incremental
+      incremental_strategy: append
+      on_schema_change: fail
+      full_refresh: false
+      contract:
+        enforced: true
+      constraints:
+        - type: not_null
+          columns: [message]
+    columns:
+      - name: message
+        description: Kafka message
+        data_type: CLOB
+        constraints:
+          - type: not_null
+
+
   - name: people
     columns:
       - name: id
diff --git a/dbt_adbs_test_project/package-lock.yml b/dbt_adbs_test_project/package-lock.yml
new file mode 100644
index 0000000..6d85e3d
--- /dev/null
+++ b/dbt_adbs_test_project/package-lock.yml
@@ -0,0 +1,6 @@
+packages:
+- package: dbt-labs/dbt_utils
+  version: 0.8.6
+- package: Snowflake-Labs/dbt_constraints
+  version: 0.4.2
+sha1_hash: 7664cb2e33183f39e86a72079d63607e43a50ad8
diff --git a/dbt_adbs_test_project/seeds/kafka_message.csv b/dbt_adbs_test_project/seeds/kafka_message.csv
new file mode 100644
index 0000000..427bcc7
--- /dev/null
+++ b/dbt_adbs_test_project/seeds/kafka_message.csv
@@ -0,0 +1,5 @@
+message
+Jack-Hunter
+Jack-Hunter
+Jack-HunterJackHunter
+Jack-Hunter