[Bug] ORA-00942 insert data to another schema

[AlexMorti]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

test model:

`{{ config(
materialized='incremental',
schema='rt_ailin'
)
}}

SELECT
11235 as id,
'alex' as first_name,
'morti' as last_name
FROM dual`

data is not inserted into another schema

START incremental model rt_ailin.customers............................... [RUN]
oracle adapter: Oracle error: ORA-00942: table or view does not exist
1 of 1 ERROR creating incremental model rt_ailin.customers...................... [ERROR in 0.33s]

logs:
another_schema.log

The error is due to the fact that the script replaces the current schema, and the schema is not needed because the global temporary table is created in the current schema anyway

dbt-oracle/dbt/include/oracle/macros/adapters.sql

Lines 354 to 355 in 461f58c

	{% set tmp_relation = base_relation.incorporate(
	path={"identifier": tmp_identifier}) -%}

fix:
{% set tmp_relation = base_relation.incorporate( path={"identifier": tmp_identifier, "schema": None}) -%}

logs after fix
dbt.log

Expected Behavior

START incremental model rt_ailin.customers............................... [RUN]
OK created incremental model rt_ailin.customers.......................... [OK in 0.49s]

Steps To Reproduce

No response

Relevant log output using --debug flag enabled

No response

Environment

- OS:MacOs
- Python:3.9
- dbt-core: 1.0.7

What Oracle database version are you using dbt with?

12.1.0

Additional Context

No response

[aosingh]

hi @AlexMorti

By default, all dbt models are built in the schema specified in your target. I see that you are using custom schema in your model configuration.
As per dbt documentation, this should create the table in <target_schema>_<custom_schema>

Did you make any changes in the macro generate_schema_name for this to work ?

[AlexMorti]

@aosingh ,yes the macro has been added

{% macro generate_schema_name(custom_schema_name, node) -%} {%- set default_schema = target.schema -%} {%- if custom_schema_name is none -%} {{ default_schema }} {%- else -%} {{ custom_schema_name | trim }} {%- endif -%} {%- endmacro %}

if you look at the insertion log into the current schema
current_schema.log

you can see what comes back table cdi_etl.o$pt_customers145317

dbt-oracle/dbt/include/oracle/macros/adapters.sql

Line 357 in 461f58c

{% do return(tmp_relation) %}

An error will occur here because the table name is already rt_ailin.o$pt_customers145557 . but it is not in the rt_ailin schema because it was created in the current user's schema

dbt-oracle/dbt/include/oracle/macros/materializations/incremental/incremental.sql

Line 50 in 461f58c

{% set build_sql = oracle_incremental_upsert(tmp_relation, target_relation, unique_key=unique_key) %}