[Bug] "Oracle error: ORA-00972: identifier is too long" with view materialization about dbt-oracle HOT 5 CLOSED

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 %}

from dbt-oracle.

@ggam

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

• Use a shorter model alias

{{ 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

from dbt-oracle.

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.

from dbt-oracle.

@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.

from dbt-oracle.

from dbt-oracle.