dbt-oracle 1.3.1 problem "oracle adapter: Oracle error: ORA-00972: identifier is too long" on dbt snapshot

[gbonazzoli]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

Same project:

dbt-oracle 1.3.0 Works fine

root@new-airflow-dbt:~/airflow/dags/SALES_ORDERS_MANAGEMENT/snapshots# dbt snapshot
05:54:07  oracle adapter: Running in cx mode
05:54:08  Running with dbt=1.3.0
05:54:08  Found 1 model, 0 tests, 3 snapshots, 0 analyses, 324 macros, 2 operations, 0 seed files, 15 sources, 0 exposures, 0 metrics
05:54:08  
05:54:08  
05:54:08  Running 1 on-run-start hook
05:54:08  1 of 1 START hook: dbt_project.on-run-start.0 .................................. [RUN]
05:54:08  1 of 1 OK hook: dbt_project.on-run-start.0 ..................................... [OK in 0.00s]
05:54:08  
05:54:08  Concurrency: 8 threads (target='prod')
05:54:08  
05:54:08  1 of 3 START snapshot psofa.sales_orders ....................................... [RUN]
05:54:08  2 of 3 START snapshot psofa.sales_orders_appog ................................. [RUN]
05:54:08  3 of 3 START snapshot psofa.uniters_orders ..................................... [RUN]
05:54:14  1 of 3 OK snapshotted psofa.sales_orders ....................................... [OK in 5.32s]
05:54:14  2 of 3 OK snapshotted psofa.sales_orders_appog ................................. [OK in 5.90s]
05:54:22  3 of 3 OK snapshotted psofa.uniters_orders ..................................... [OK in 13.28s]
05:54:22  
05:54:22  Running 1 on-run-end hook
05:54:22  1 of 1 START hook: dbt_project.on-run-end.0 .................................... [RUN]
05:54:22  1 of 1 OK hook: dbt_project.on-run-end.0 ....................................... [OK in 0.00s]
05:54:22  
05:54:22  
05:54:22  Finished running 3 snapshots, 2 hooks in 0 hours 0 minutes and 14.15 seconds (14.15s).
05:54:22  
05:54:22  Completed successfully
05:54:22  
05:54:22  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3```

dbt-oracle 1.3.1 gives the following output:

root@new-airflow-dbt:~/airflow/dags/SALES_ORDERS_MANAGEMENT/snapshots# dbt snapshot
05:43:33 oracle adapter: Running in cx mode
05:43:33 Running with dbt=1.3.1
05:43:33 Found 1 model, 0 tests, 3 snapshots, 0 analyses, 325 macros, 2 operations, 0 seed files, 15 sources, 0 exposures, 0 metrics
05:43:33
05:43:34
05:43:34 Running 1 on-run-start hook
05:43:34 1 of 1 START hook: dbt_project.on-run-start.0 .................................. [RUN]
05:43:34 1 of 1 OK hook: dbt_project.on-run-start.0 ..................................... [OK in 0.00s]
05:43:34
05:43:34 Concurrency: 8 threads (target='prod')
05:43:34
05:43:34 1 of 3 START snapshot psofa.sales_orders ....................................... [RUN]
05:43:34 2 of 3 START snapshot psofa.sales_orders_appog ................................. [RUN]
05:43:34 3 of 3 START snapshot psofa.uniters_orders ..................................... [RUN]
05:43:34 oracle adapter: Oracle error: ORA-00972: identificativo troppo lungo
05:43:34 3 of 3 ERROR snapshotting psofa.uniters_orders ................................. [ERROR in 0.46s]
05:43:35 oracle adapter: Oracle error: ORA-00972: identificativo troppo lungo
05:43:35 2 of 3 ERROR snapshotting psofa.sales_orders_appog ............................. [ERROR in 0.84s]
05:43:39 1 of 3 OK snapshotted psofa.sales_orders ....................................... [OK in 4.96s]
05:43:39
05:43:39 Running 1 on-run-end hook
05:43:39 1 of 1 START hook: dbt_project.on-run-end.0 .................................... [RUN]
05:43:39 1 of 1 OK hook: dbt_project.on-run-end.0 ....................................... [OK in 0.00s]
05:43:39
05:43:39
05:43:39 Finished running 3 snapshots, 2 hooks in 0 hours 0 minutes and 5.84 seconds (5.84s).
05:43:39
05:43:39 Completed with 2 errors and 0 warnings:
05:43:39
05:43:39 Database Error in snapshot uniters_orders (snapshots/uniters_orders.sql)
05:43:39 ORA-00972: identificativo troppo lungo
05:43:39
05:43:39 Database Error in snapshot sales_orders_appog (snapshots/sales_orders_appog.sql)
05:43:39 ORA-00972: identificativo troppo lungo
05:43:39
05:43:39 Done. PASS=1 WARN=0 ERROR=2 SKIP=0 TOTAL=3```

Expected Behavior

It is logical to expect the same behaviour.

Steps To Reproduce

1. pip install --upgrade dbt-oracle==1.3.1
2. dbt snapshot

Relevant log output using --debug flag enabled

root@new-airflow-dbt:~/airflow/dags/SALES_ORDERS_MANAGEMENT/snapshots# dbt debug
05:58:19  Running with dbt=1.3.1
dbt version: 1.3.1
python version: 3.8.10
python path: /root/dbt/bin/python3
os info: Linux-5.4.17-2136.307.3.5.el8uek.x86_64-x86_64-with-glibc2.29
Using profiles.yml file at /root/.dbt/profiles.yml
Using dbt_project.yml file at /root/airflow/dags/SALES_ORDERS_MANAGEMENT/dbt_project.yml

05:58:19  oracle adapter: Running in cx mode
Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

Connection:
  user: psofa
  database: psdb
  schema: psofa
  protocol: tcp
  host: 172.16.3.32
  port: 1521
  tns_name: PSDBPROD
  service: psdb
  connection_string: None
  shardingkey: []
  supershardingkey: []
  cclass: None
  purity: None
  retry_count: 1
  retry_delay: 3
  Connection test: [OK connection ok]

All checks passed!

Environment

- OS: Ubuntu 20.04.5 LTS
- Python: Python 3.8.10
- dbt: 1.3.1

What Oracle database version are you using dbt with?

11.2.0.4

Additional Context

No response

[aosingh]

Hi @gbonazzoli

Could you run the dbt snapshot command with --debug option? This will show the SQL and identifier which raises this error.

dbt --debug snapshot

I think from version 12.2 SQL identifiers can be 128 bytes long

Also, could you let me know the dbt snapshot strategy (timestamp or check) configured ?

[gbonazzoli]

Hi @aosingh

You can find the debug file attached to the present message.

dbt-31-error.txt

The strategy is:

{{
    config(
      target_database='psdb',
      target_schema='psofa',
      unique_key='ID',

      strategy='check',
      check_cols='all',
      invalidate_hard_deletes=True
    )
}}

[tmirko]

Hi,

I'm getting the same error when trying to materialize an incremental model,

It looks like the assigned names for when creating temporary tables are too long now.

example with 1.3.0: create global temporary table o$pt_(model name)132935

example with 1.3.1: create global temporary table o$pt_(model name)133255833329

[aosingh]

@Mirko-T @gbonazzoli

This will be an issue with older versions of Oracle Databases. I think from version 12.2 SQL identifiers can be 128 bytes long

To resolve this, include the following macro in your dbt project.

{% macro oracle__make_temp_relation(base_relation, suffix) %}
    {% set dt = modules.datetime.datetime.now() %}
    {% set dtstring = dt.strftime("%H%M%S") %}
    {% set tmp_identifier = 'o$pt_' ~ base_relation.identifier ~ dtstring %}
    {% set tmp_relation = base_relation.incorporate(
                                path={"identifier": tmp_identifier, "schema": None}) -%}

    {% do return(tmp_relation) %}
{% endmacro %}

[gbonazzoli]

@aosingh It works! What I have done is:

In every dbt project, I have created a file named macros/make_temp_relation.sql with the content you suggested me.

Do you think the "Oracle identifier's length problem" will be directly fixed in a future version of the oracle adapter ?

[aosingh]

@gbonazzoli

Starting from database version 12.2 the identifiers can be 128 bytes long and hence the identifier length will not be problem.

You do not need the macro override for newer Database versions (12.2, 18c, 19c, 21c). The macro override is only needed for older versions (11.2.0.4 in this case).

[ggam]

@aosingh this same problem was fixed upstream for PostgreSQL (dbt-labs/dbt-core#2869). Do you think a similar fix could be officially included on the Oracle adapter too?

I understand we are talking about very old Oracle versions, but it's an annoying limitation. I was putting cryptic alias for all my materializations until I found this issue.

[ggam]

Btw, my particular case is when using views. It seems like at some point a backup view is created and then deleted:

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 view ********.********************__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;