oracle/dbt-oracle

[Bug] "Oracle error: ORA-00972: identifier is too long" with view materialization

ggam opened this issue · 5 comments

ggam commented

Is there an existing issue for this?

  • I have searched the existing issues

Current Behavior

Related to #62.

I have a model with 20 characters, materialized as a view. On dbt build,

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

Line 255 in f00b711

{% macro oracle__drop_relation(relation) -%}
is executed, trying to drop a non existent view, which happens to be 32 characters long:

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;

This fails on Oracle 11g but the funny thing is the code wouldn't have any effect as there's no backup view to delete.

For now, I've overriden the macro's content to:

{% macro oracle__drop_relation(relation) -%}
  {% if relation.type != "view" %}
    {% call statement('drop_relation', auto_begin=False) -%}
    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 {{ relation.type }} {{ relation }} 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;
    {%- endcall %}
  {% endif %}
{% endmacro %}

It covers my specific case for now but I'm sure there's more to it so I won't submit such a PR.

Note that the problem of the "too long" identifiers was already fixed upstream for PostgreSQL (dbt-labs/dbt-core#2869). It would be very appreciated if something like that could be done for Oracle too.

Expected Behavior

I should be able to use any name length that's acceptable for the DB.

Steps To Reproduce

Create a 20 characters long model name, materialized as a view and build it multiple times.

Relevant log output using --debug flag enabled

No response

Environment

- OS: Windows 
- Python: 3.11
- dbt: 1.4.2

What Oracle database version are you using dbt with?

11g

Additional Context

No response

aosingh commented

@ggam

For any ORA-00972: identifier is too long issue, if the database version is older than 12c there are 2 options:

{{ config(alias='sessions') }}
  • Have a macro override at dbt project level like we did in a similar issue

From Oracle 12c, identifiers can be 128 bytes long

I checked your macro override and I would be careful adding this check to a generic macro

{% if relation.type != "view" %}

The reason why we have <relation>__dbt_backup, is to handle the case if the dbt model was a previously a table. dbt renames the old relation to <relation>__dbt_backup and for that it drops any existing <relation>__dbt_backup to avoid ORA-00955: name is already used by an existing object

ggam commented

Hi @aosingh thanks for the quick response. I'm using alias for the longest names, but a "__dbt_backup" suffix is 12 characters long. That means I only have 18 characters to use in my user facing view names.

Since this is internal dbt and temporary stuff, I think it'd be correct to truncate the names from the left to 30 characters for old db versions. The rollback mechanism should prevent those objects to be persisted in case of error.

Regarding my hack:

{% if relation.type != "view" %}

Thanks for the word of caution. I'm aware it's a little clunky, but it should work until I have some time to dig deeper into the code.

ggam commented

Thanks to your comments I came to a safer workaround. Just ignore that especific exception:

{% macro oracle__drop_relation(relation) -%}
    {% call statement('drop_relation', auto_begin=False) -%}
    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);
        identifier_too_long  EXCEPTION;
        PRAGMA EXCEPTION_INIT(identifier_too_long, -972);
    BEGIN
        SAVEPOINT start_transaction;
        EXECUTE IMMEDIATE 'DROP {{ relation.type }} {{ relation }} 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;
        WHEN identifier_too_long THEN
            NULL; -- object cannot actually exist anyway
    END;
    {%- endcall %}
{% endmacro %}
  • 👍1
aosingh commented

@ggam

I discussed this internally, and as documented, the minimum supported Oracle Database version by dbt-oracle is 12c.

Starting with Oracle 12c, identifiers can be 128 bytes long and this should not be problem.

With Oracle 11g, let me know in case you encounter any ORA-00972: identifier is too long issues. I can help suggest a workaround probably by overriding the macro at project level.

ggam commented