[FEATURE] dbtvault Oracle Support

[DebanjanBanerjeeQB]

Describe the bug
When trying to create dbtvault warehouse on Oracle DB there are issues that are being faced

1. Generated SQL uses native snowflake functions and needs heavy modifications on the package to make it work

MD5_Binary , CONCAT_WS does not exist on Oracle.

2. According to the example project, the compiled SQL that is being created has nested with clause that is not supported by Oracle

Environment

dbt version: 1.1.1
dbtvault version: 5.3
Database/Platform: Oracle

To Reproduce
Steps to reproduce the behavior:

1. Connect Oracle
2. Create a new dbt project with dbtvault package
3. Connect to Oracle using the quick start guide
4. Try to run your staging command dbt run -s v_......
5. See error
6. Go to target > compiled > profile_name > code.sql to see compiled sql to see compiled sql with syntax issues

Expected behavior
Compiled SQL coming out of the blackbox should not show syntax errors

Screenshots
See above

Log files
Found 3 models, 0 tests, 0 snapshots, 0 analyses, 561 macros, 0 operations, 0 seed files, 1 source, 0 exposures, 0 metrics 07:51:03 07:51:03 Concurrency: 1 threads (target='dev') 07:51:03 07:51:03 1 of 1 START view model system.v_claim_header .................................. [RUN] 07:51:03 oracle adapter: Oracle error: ORA-32034: unsupported use of WITH clause 07:51:03 1 of 1 ERROR creating view model system.v_claim_header ......................... [ERROR in 0.22s] 07:51:03 07:51:03 Finished running 1 view model in 0.52s. 07:51:03 07:51:03 Completed with 1 error and 0 warnings: 07:51:03 07:51:03 Database Error in model v_claim_header (models/stage/v_claim_header.sql) 07:51:03 ORA-32034: unsupported use of WITH clause 07:51:03 compiled SQL at target/run/dbt_poc/models/stage/v_claim_header.sql 07:51:03 07:51:03 Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Additional context
Add any other context about the problem here.

AB#5372

[DVAlexHiggs]

Hi thanks for the report. This cannot be considered a bug because we do not support Oracle. Our currently supported databases are Snowflake, BigQuery and SQLServer.

Soon we have Databricks and Postgres coming. We do not have plans to support Oracle in the near future.

I will keep this open because we will soon be providing better contribution guidelines for the community to enable users to contribute platform support themselves

[DebanjanBanerjeeQB]

Hello! Thanks for the prompt response. Is the dbt-oracle package available on pypi an OS Contribution ? Tricky thing is that we can seamlessly connect to Oracle using this document. But the syntaxes are not influenced by it. Or it is just to establish connections and dbtvault support is treated differently ? Thanks!

[DVAlexHiggs]

dbt-oracle is vendor supported. See this list. For packages (like dbtvault) you need to write adapters for each platform, see here. You may also write generic-enough SQL that it works on multiple platforms, which we have done where we can (though we would like to improve this!).

So in short, dbtvault does not support Oracle currently because we have not written the adapter for it. dbt itself does support Oracle.

If you want to write your own macros for dbtvault with Oracle compatible SQL, take a look here. Hope this helps