[Bug] Case sensitive names for Oracle

[mdopp]

Is there an existing issue for this?

• [X] I have searched the existing issues

Current Behavior

dbt run is not refreshing or creating tables and views, if the file contains lower-case characters

Expected Behavior

dbt run should find the files, at least if the case matches, but in general should even ignore the case.

A solution could be that tables / views are create using "" to keep the case as given. The other way would be to completly ignore the case given, and always work with oracle typical upper-case.

Steps To Reproduce

1. Environment

oracle adapter: Running in thin mode
Core:
  - installed: 1.7.11
  - latest:    1.8.0  - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - oracle:    1.7.11 - Ahead of latest version!
  - postgres:  1.7.11 - Update available!
  - snowflake: 1.7.3  - Update available!

2. create a file (table or view) in lowercase characters.

3. dbt run

Compilation Error in model int_kunden__joined (models\intermediate\int_kunden__joined.sql)
  When searching for a relation, dbt found an approximate match. Instead of guessing
  which relation to use, dbt will move on. Please delete ROADCORE.INT_KUNDEN__JOINED, or rename it to be less ambiguous.
  Searched for: ROADCORE.INT_KUNDEN__JOINED
  Found: ROADCORE.INT_KUNDEN__JOINED

  > in macro materialization_view_oracle (macros\materializations\view\view.sql)
  > called by model int_kunden__joined (models\intermediate\int_kunden__joined.sql)

As you see, the system states that it has found exactly the same as it searched fore, still has a problem with it.

Most likely reason: Oracle thinks in upper case, while the file and references are noted in lower_case.

Relevant log output using --debug flag enabled

No response

Environment

- OS: Win 11
- Python: 
- dbt: 1.7.11

What Oracle database version are you using dbt with?

Oracle version 19

Additional Context

No response

[aosingh]

Hi @mdopp

We have seen this approximate match issue before. Could you please let me know the following ?

• is this issue seen in an On-prem Database or Autonomous Database ?

• is the database name uppercase or lowercase ? You can check that by running the following query.

select SYS_CONTEXT('userenv', 'DB_NAME') from dual;

• Does the database name returned above match the database name which you have defined in dbt profiles.yml file?

• What is the quoting configuration in dbt-project.yml file?

[mdopp]

• is this issue seen in an On-prem Database or Autonomous Database ?

This is an On-Prem Database

• is the database name uppercase or lowercase ? You can check that by running the following query.

the name is in lowercase

• Does the database name returned above match the database name which you have defined in dbt profiles.yml file?

i used lowercase chars for everything in the profile.yml (service, schema, user), and it is the same => service = SYS_CONTEXT('userenv', 'DB_NAME')

• What is the quoting configuration in dbt-project.yml file?

no explizit quoting configuration is specified in the dbt-project.yml

Another hint => It worked with postgres, just switched to oracle (and had to change some parts, as this oracle version did not support boolean). Everything else stayed the same.

[mdopp]

update: changed profile.yml user/schema to upper case, and also the filename to upper case, still the same error

[aosingh]

@mdopp Keep the database name in lowercase.

could you test after enabling quoting for database name in dbt_project.yml file ?

quoting:
  database: true

[mdopp]

"quoting database" works like a charm. THANK YOU.