Forum Stats

  • 3,824,977 Users
  • 2,260,447 Discussions
  • 7,896,368 Comments

Discussions

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

2

Answers

  • User_I4Y3C
    User_I4Y3C Member Posts: 16 Green Ribbon
    edited Jun 24, 2020 8:24AM

    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
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    edited Jun 24, 2020 8:33AM

    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
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    edited Jun 24, 2020 8:38AM

    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
  • User_I4Y3C
    User_I4Y3C Member Posts: 16 Green Ribbon
    edited Jun 24, 2020 8:41AM

    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
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    edited Jun 24, 2020 8:48AM

    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
    Tubby Member Posts: 6,987 Bronze Crown
    edited Jun 24, 2020 8:48AM
    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
  • User_I4Y3C
    User_I4Y3C Member Posts: 16 Green Ribbon
    edited Jun 24, 2020 8:54AM

    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
    Solomon Yakobson Member Posts: 19,456 Red Diamond
    edited Jun 24, 2020 9:05AM

    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
    Tubby Member Posts: 6,987 Bronze Crown
    edited Jun 24, 2020 9:48AM
    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
    User_I4Y3C Member Posts: 16 Green Ribbon
    edited Jun 24, 2020 12:58PM

    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