Create a Sequence, through a Trigger. Oracle

Question

I’m trying to create a sequence in a trigger.

When trying to create by Execute Immediate, I’m getting the following feedback:

ORA: 4092 - Cannot commit in a Trigger.

For all I’ve researched, use the Execute Immediate in a Trigger is not possible as he is in a dll that issues a commit automatic.

I wonder if there is any other way to create a Quence for a Rigger.

Follow the code of Trigger

CREATE OR REPLACE TRIGGER "TR_TEST"  
  AFTER UPDATE ON TEST_TABLE
  FOR EACH ROW
DECLARE
BEGIN
   BEGIN
     EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ01' ||
                       '      MINVALUE 1' ||
                       '      MAXVALUE 999999999999999999999999999' ||
                       '      START WITH 1' ||
                       '      INCREMENT BY 1' ||
                       '      NOCACHE';      
   EXCEPTION
       WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20200, 'Error');
   END;  
END 

Answer 1

A Trigger should not create an object. Each time it is fired, it will attempt to create the object again... From what I understand, you want to increment the SEQ01 sequence after the TEST_TABLE table update. Right? If so, within the immediate run it should contain:

select SEQ01.nextval from dual;

Since you created Quence earlier.

Answer 2

You can create the sequence in a precedent and call the precedent inside your Rigger so it will be possible to create or use the sequence.

Take a look at this link: https://stackoverflow.com/questions/40016510/creating-and-using-sequence-in-oracle-stored-procedure-sequence-doesnt-exist

But I believe that the best practice would be to create the normal sequence given that you already know that it should exist.