Sequence Trigger (exclude number range) Fails wtih ORA-01403: no data found

User_I4Y3C

I was thinking something like:

    BEGIN

      :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      IF :new.SEQ BETWEEN '3' AND '4'

      THEN

        BEGIN

          EXECUTE IMMEDIATE 'ALTER SEQUENCE MYSCHEMA.TEST_SEQ RESTART START WITH 5';

          :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

But still I can't get past the ORA-ERROR. Even the simplest trigger won't work.

Solomon Yakobson

Something doesn't add up here:

SQL> INSERT INTO MYSCHEMA.TEST_SEQ (item) SELECT 1 FROM DUAL CONNECT BY LEVEL <= 13;

INSERT INTO MYSCHEMA.TEST_SEQ (item) SELECT 1 FROM DUAL CONNECT BY LEVEL <= 13

                  *

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "MYSCHEMA.TEST_TRIG", line 2

ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'

And:

CREATE OR REPLACE TRIGGER MYSCHEMA.TEST_TRIG

BEFORE INSERT OR UPDATE ON MYSCHEMA.SEQ_TEST

FOR EACH ROW

    BEGIN

      :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      IF :new.SEQ = 3

        THEN

        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      END IF;

      IF :new.SEQ = 4

        THEN

        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      END IF;

    END;

Insert statement inserts into TEST_SEQ while based on your trigger TEST_SEQ is sequence name, not table name and table name is SEQ_TEST.

SY.

Solomon Yakobson

You were told already - you can't execute DDL from a trigger since DDL does implicit commit. You could do it as autonomous transaction but changes will not be visible to main transaction and you will have to use dynamic SQL all over the place. And don't forget there can be multiple sessions inserting. Anyway, modifying sequence on the fly is FLAWED approach.

SY.

User_I4Y3C

I think we would have received a different error if I didn't have the right table name sir. I am simply editing the names so I don't put the real table name or trigger name.

This is the problem:

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "MYSCHEMA.TEST_TRIG", line 2

ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'

Someone said it might be permission issues? I created them in MYSCHEMA as system, and run the insert as system. But even if I GRANT the error still shows up.

Solomon Yakobson

SQL> CREATE USER MYSCHEMA IDENTIFIED BY MYSCHEMA

  2  DEFAULT TABLESPACE USERS

  3  QUOTA UNLIMITED ON USERS

  4  /

User created.

SQL> GRANT CREATE SESSION,

  2        CREATE TABLE,

  3        CREATE SEQUENCE,

  4        CREATE TRIGGER

  5    TO MYSCHEMA

  6  /

Grant succeeded.

SQL> CONNECT [email protected]/MYSCHEMA

Connected.

SQL> CREATE TABLE MYSCHEMA.SEQ_TEST(

  2                                 SEQ  NUMBER,

  3                                 ITEM NUMBER

  4                                )

  5  /

Table created.

SQL> CREATE SEQUENCE MYSCHEMA.TEST_SEQ

  2    MAXVALUE 10

  3    CYCLE

  4    CACHE 5

  5  /

Sequence created.

SQL> CREATE OR REPLACE

  2    TRIGGER MYSCHEMA.TEST_TRIG

  3    BEFORE INSERT

  4        OR UPDATE

  5    ON MYSCHEMA.SEQ_TEST

  6    FOR EACH ROW

  7    BEGIN

  8        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

  9        WHILE :new.SEQ IN (3,4) LOOP

10          :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

11        END LOOP;

12

13      END;

14  /

Trigger created.

SQL> INSERT

  2    INTO MYSCHEMA.SEQ_TEST(item)

  3      SELECT  1

  4        FROM DUAL

  5        CONNECT BY LEVEL <= 13

  6  /

13 rows created.

SQL> SELECT  *

  2    FROM  MYSCHEMA.SEQ_TEST

  3  /

       SEQ       ITEM

---------- ----------

         1          1

         2          1

         5          1

         6          1

         7          1

         8          1

         9          1

        10          1

         1          1

         2          1

         5          1

13 rows selected.

SQL>

SY.

Tubby

4249458 wrote:Tubby - This was just an example, obviously no one would every use a sequence between 1-10, but why test with a sequence between 1 - 10000?I originally had the DDL in a separate package that was being called by the trigger - but consolidated it to limit the number of things to be created while testing.I'm not sure what's unclear about the requirments:1) I have a table with a column that must be updated with a unique value moving forward (upon each new insert) *For example a number range between 1 - 10 (because the table column is varchar2(2))2) There are currently values in this column that can NOT be deleted *Numbers 3 and 4 are already in the table - and can NOT be deleted3) The numbers should overwrite any manual entry input by the user to the new sequence4) When the sequence cycles - it should still skip numbers 3 and 4

I think perhaps the reason it isn't clear is because you are insisting you need to take this technical approach to solve your problem yet haven't put it in context of what business value it provides. I can't imagine this being a business requirement, and the need to do this technically isn't well founded. If you need a technical solution to have a unique number generated and you already have some numbers you need to avoid a collision with then you would use a value that can't possibly be present. For example if you had random values up to 10,000 that couldn't be used you would start the sequence at 10001 and always use nextval, no trickery in triggers, no confusion.

So although your technical requirements are clear, it's highly unlikely what you propose is actually the best solution.

Cheers,

User_I4Y3C

True -

I agree and that was something I considered - but the end result is still the same:

This is the problem:

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "MYSCHEMA.TEST_TRIG", line 2

ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'

I was trying to kill two birds with one stone and maybe added confusion to the real issue at hand.

Solomon Yakobson

I have no Idea what are you doing. Provide complete output showing create table, create sequence, create trigger and insert statements. Or better run:

DROP TABLE MYSCHEMA.SEQ_TEST PURGE

/

DROP SEQUENCE MYSCHEMA.TEST_SEQ

/

CREATE TABLE MYSCHEMA.SEQ_TEST(

                               SEQ  NUMBER,

                               ITEM NUMBER

                              )

/

CREATE SEQUENCE MYSCHEMA.TEST_SEQ

  MAXVALUE 10

  CYCLE

  CACHE 5

/

CREATE OR REPLACE

  TRIGGER MYSCHEMA.TEST_TRIG

  BEFORE INSERT

      OR UPDATE

  ON MYSCHEMA.SEQ_TEST

  FOR EACH ROW

  BEGIN

      :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      WHILE :new.SEQ IN (3,4) LOOP

        :new.SEQ := MYSCHEMA.TEST_SEQ.NEXTVAL;

      END LOOP;

    END;

/

INSERT

  INTO MYSCHEMA.SEQ_TEST(item)

    SELECT  1

      FROM DUAL

      CONNECT BY LEVEL <= 13

/

SELECT  *

  FROM  MYSCHEMA.SEQ_TEST

/

SY.

Tubby

4249458 wrote:True - I agree and that was something I considered - but the end result is still the same:This is the problem:ERROR at line 1:ORA-01403: no data foundORA-06512: at "MYSCHEMA.TEST_TRIG", line 2ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'I was trying to kill two birds with one stone and maybe added confusion to the real issue at hand.

Ideally you'd strip all the fluff away from the trigger, start with :new.column_value := sequence.nextval and then add in pieces from there. That should give you a good idea where the code is failing. Or post the complete piece of code here, not one you're mocking up for the forum but the actual code (redact names if you need to).

I would highly encourage you to abandon the current approach though and go with something simple like simply starting the sequence outside the existing values range.

Cheers,

User_I4Y3C

This worked great -

I updated the loop to use a between instead:

WHILE :new.SEQ BETWEEN ''3'' AND ''4''

The ORA-01403 error was a permissions related issue. I recreated it under a user schema and everything worked fine.

Thank you gentlemen @Solomon Yakobson @Tubby