Thank you @ThoSap for testing and contributing.

While we review to get this merged, could you sign the Oracle Contributor Agreement (OCA) ?

The bottom of your commit message must have the following line using your name
and e-mail address as it appears in the OCA Signatories list.

Signed-off-by: Your Name <you@example.org>

This can be automatically added to pull requests by committing with:

git commit --signoff

I just wanted to create an Individual OCA but the oracle/dbt-oracle repo is not available for selection and it is a required field.
https://oca.opensource.oracle.com/api/v1/projects/enabled

Can you help @aosingh ?

Sure, let me check and I will get back to you

Hi @ThoSap

Thanks once again for your help!

I would like to better understand the problem and the fixes. The reason is, I am trying to reproduce the issue but it works as expected with the current version of dbt-oracle.

Test Model - https://github.com/oracle/dbt-oracle/blob/main/dbt_adbs_test_project/models/promotion_costs.sql
Oracle Database version - 19c
dbt_project.yml - https://github.com/oracle/dbt-oracle/blob/main/dbt_adbs_test_project/dbt_project.yml

I ran the model 3 times and each time it completed successfully. With --debug flag enabled i could also see the backup table being dropped using the below PL/SQL.

DECLARE
     dne_942    EXCEPTION;
     PRAGMA EXCEPTION_INIT(dne_942, -942);
     attempted_ddl_on_in_use_GTT EXCEPTION;
     pragma EXCEPTION_INIT(attempted_ddl_on_in_use_GTT, -14452);
  BEGIN
     SAVEPOINT start_transaction;
     EXECUTE IMMEDIATE 'DROP table dbt_test.promotion_costs__dbt_backup cascade constraint';
     COMMIT;
  EXCEPTION
     WHEN attempted_ddl_on_in_use_GTT THEN
        NULL; -- if it its a global temporary table, leave it alone.
     WHEN dne_942 THEN
        NULL;
  END;

The thing with this change is that it would no longer be necessary to specify the database name (in profiles.yml) which normally is never needed in any other application using OJDBC or cx_Oracle when connecting to an Oracle DB.
The database name (select SYS_CONTEXT('userenv', 'DB_NAME') from dual;) is not required for OJDBC, cx_Oracle and not even in SQL Developer, but only the service name or SID instead.

I don't think the database name property is needed at all as the service name should suffice with these PR changes, maybe we can eliminate it altogether.
But it could be that I don't get the technical reason why the database name is really needed in DBT Oracle 🤔

This probably has historic reasons and also there is the confusion, where if one specifies the profiles.yml property dbname but not database and also omits service, the property dbname with alias database gets to be the service name and then the connection works anyways.
https://github.com/oracle/dbt-oracle/blob/v1.0.0/dbt/adapters/oracle/connections.py#L129-L134
Example profiles.yml:

config:
  partial_parse: true
  printer_width: 120
  send_anonymous_usage_stats: false
  use_colors: true
dbt_test:
  target: prod
  outputs:
    prod:
      type: oracle
      protocol: tcp
      host: yourhost.example.com
      port: 1521
      schema: test
      user: test
      pass: SECRET_PW
      # Connection works anyways as in connections.py#L83 the dbname property gets alias database
      # and if the service property is not specified the database internally is the service in the end
      # https://github.com/oracle/dbt-oracle/blob/v1.0.0/dbt/adapters/oracle/connections.py#L134
      dbname: test.service.name.example.com
      threads: 4

For my use case using Airbyte (I plan to do there a PR also with the new oracle/dbt-oracle adapter), the database property in profiles.yml would pose a breaking change in the Oracle Destination Connector, as suddenly users need to specify the database name (select SYS_CONTEXT('userenv', 'DB_NAME') from dual;) as a mandatory field if they want to use the Oracle Basic Normalization which is already fully implemented in Airbyte but currently not enabled due to the fact that the current Airbyte Oracle normalization implementation is still using DBT 0.19.1 from techindicium/dbt-oracle:0.4.3.

Also the OracleIncludePolicy for database is False anyways 😁
https://github.com/oracle/dbt-oracle/blob/v1.0.0/dbt/adapters/oracle/relation.py#L32

Thanks for explaining this to me. I understand the following.

• You would like to keep dbname or database as optional parameter in connection config. This way it will not be a breaking change for Airbyte. Also, you can create a PR there to use the new dbt adapter

• Secondly, dbname or database is redundant in the WHERE clause in oracle__list_relations_without_caching macro and we can safely remove it. Thus we can leave it empty or None

Let me know if this understanding is correct.

Could we revert the change in dbt/adapters/oracle/impl.py because in OracleIncludePolicy the property database is False. Additionally IncludePolicy is configurable in dbt_project.yml ?

Without this change it is not working, even though for OracleIncludePolicy the property database is False.

Could we revert the change in dbt/adapters/oracle/impl.py because in OracleIncludePolicy the property database is False. Additionally IncludePolicy is configurable in dbt_project.yml ?

Without this change it is not working, even though OracleIncludePolicy the property database is False.

Ok, I will check this and let you know.

Thanks for explaining this to me. I understand the following.

• You would like to keep dbname or database as optional parameter in connection config. This way it will not be a breaking change for Airbyte. Also, you can create a PR there to use the new dbt adapter
• Secondly, dbname or database is redundant in the WHERE clause in oracle__list_relations_without_caching macro and we can safely remove it. Thus we can leave it empty or None

Let me know if this understanding is correct.

• I would like to remove dbname, database and env var DBT_ORACLE_DATABASE alltogether as it makes no sense at all. It does not matter if one connects using OracleConnectionMethod -> HOST, TNS or CONNECTION_STRING, you will always be on the same database (select SYS_CONTEXT('userenv', 'DB_NAME') from dual;) and therefore also the function impl.py#verify_database can never throw an exception, not even with a DB link.

• The WHERE clause in macros
  oracle__list_relations_without_caching
  and
  oracle__get_columns_in_relation
  also do not make sense at all due to the first point.
  We do not have to filter a column just for the sake that it exists in the first place.

Maybe I'm missing the technical reason but why was this dbname, database and env var DBT_ORACLE_DATABASE introduced in the first place?
Could it be that with the Oracle Autonomous Database Service (ADBS) it is possible to have multiple database (select SYS_CONTEXT('userenv', 'DB_NAME') from dual;) names with the same service/connection as I never used ADBS before.

Thanks for explaining this to me. I understand the following.

• You would like to keep dbname or database as optional parameter in connection config. This way it will not be a breaking change for Airbyte. Also, you can create a PR there to use the new dbt adapter
• Secondly, dbname or database is redundant in the WHERE clause in oracle__list_relations_without_caching macro and we can safely remove it. Thus we can leave it empty or None

Let me know if this understanding is correct.

* I would like to **remove** `dbname`, `database` and env var `DBT_ORACLE_DATABASE` alltogether as it makes no sense at all. It does not matter if one connects using `OracleConnectionMethod` -> `HOST`, `TNS `or `CONNECTION_STRING`, you will always be on the same database (`select SYS_CONTEXT('userenv', 'DB_NAME') from dual;`) and therefore also the function [impl.py#verify_database](https://github.com/oracle/dbt-oracle/blob/v1.0.0/dbt/adapters/oracle/impl.py#L101-L112) can _never_ throw an exception, not even with a DB link.

* The WHERE clause in macros
  [`oracle__list_relations_without_caching`](https://github.com/oracle/dbt-oracle/blob/v1.0.0/dbt/include/oracle/macros/adapters.sql#L344)
  and
  [`oracle__get_columns_in_relation`](https://github.com/oracle/dbt-oracle/blob/v1.0.0/dbt/include/oracle/macros/adapters.sql#L185-L187)
  also do not make sense at all due to the first point.
  We do not have to filter a column just for the sake that it exists in the first place.

Maybe I'm missing the technical reason but why was this dbname, database and env var DBT_ORACLE_DATABASE introduced in the first place? Could it be that with the Oracle Autonomous Database Service (ADBS) it is possible to have multiple database (select SYS_CONTEXT('userenv', 'DB_NAME') from dual;) names with the same service/connection as I never used ADBS before.

agree and would love to see this simplification/fix getting merged.

Maybe I'm missing the technical reason but why was this dbname, database and env var DBT_ORACLE_DATABASE introduced in the first place?

This was present in Techindicium's version and we have kept it for compatibility reasons. I do see your point. Even in ADBS we will not have multiple values for db_name.

Also, even in dbt-core v0.19 (one used by Airbyte), database is a mandatory parameter in the Credentials class. These contracts are defined by dbt-core. Maybe this is also why database or dbname existed in dbt-oracle

https://github.com/dbt-labs/dbt-core/blob/0.19.latest/core/dbt/contracts/connection.py#L120

I will come up with a better solution for these issues.

I just wanted to create an Individual OCA but the oracle/dbt-oracle repo is not available for selection and it is a required field. https://oca.opensource.oracle.com/api/v1/projects/enabled

Can you help @aosingh ?

Do you have an update on this @aosingh ?
I would sign the OCR in the meantime.

I'm still trying to fix the following error, help would be appreciated 😁

14:46:57.198298 [error] [MainThread]: Encountered an error:
2022-05-06 14:46:57 normalization > non-default argument 'schema' follows default argument

Thank you @ThoSap for testing and contributing.

While we review to get this merged, could you sign the Oracle Contributor Agreement (OCA) ?

The bottom of your commit message must have the following line using your name and e-mail address as it appears in the OCA Signatories list.

Signed-off-by: Your Name <you@example.org>

This can be automatically added to pull requests by committing with:

git commit --signoff

I signed off all commits using my OCA agreement using rebase, sorry for the commit spam here.
https://docs.github.com/en/pull-requests/committing-changes-to-your-project/creating-and-editing-commits/changing-a-commit-message
https://hyperledger-indy.readthedocs.io/projects/sdk/en/latest/docs/contributors/signing-commits.html#signing-your-current-commit

Superseded by #6