CODEWITHSUNDEEP
oracle-databasetriggers
Annabett
Annabett

Reputation: 89

ORA-00604 when using ora_sql_txt

I have a trigger using the function ora_sql_txt

I want to use a create table statement with length of around 2100 chars.

On one database, the trigger is working. On the other database, I get the error:

ORA-04088: Fehler bei der Ausführung von Trigger 'TESTUSER.TRIGGERNAME' ORA-00604: Fehler auf rekursiver SQL-Ebene 1 ORA-06502: PL/SQL: numerischer oder Wertefehler: Zeichenfolgenpuffer zu klein ORA-06512: in Zeile 42 04088. 00000 - "error during execution of trigger '%s.%s'" *Cause: A runtime error occurred during execution of a trigger. *Action: Check the triggers which were involved in the operation.

I broke down the trigger to this:

create or replace TRIGGER SCHEMAUSER.TRIGGERNAME
    AFTER DDL ON SCHEMAUSER.SCHEMA
    DECLARE
         sql_text ora_name_list_t;
         n PLS_INTEGER;

    BEGIN
       IF (ora_sysevent ='CREATE' AND  ora_dict_obj_type = 'TABLE') THEN

          -- Get statement
          n := ora_sql_txt(sql_text);
          
       END IF;
    END;

Both databases are using the same oracle version 12.2.

Can somehow the values for PLS_INTEGER be changed? Does anybody have an idea why this is not working?

Upvotes: 1

Views: 419

Answers (1)

Annabett
Annabett

Reputation: 89

I was logged in as another user that had the same trigger. Even though I changed another schema, the trigger for the schemauser I was logged in as was triggered. This caused the issue. Case closed.

Upvotes: 0

Related Questions