Forum Stats

  • 3,826,070 Users
  • 2,260,592 Discussions
  • 7,896,782 Comments

Discussions

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

User_I4Y3C
User_I4Y3C Member Posts: 16 Green Ribbon
edited Jun 24, 2020 3:02PM in SQL & PL/SQL

Hello PL/SQL Experts,

I need your help. … Banging my head on the wall trying to make this simple sequence trigger work. …..

BACKGROUND:

a) I need a sequence that will update a column with a number between (1 - 10) [the column is VARCHAR2(2) datatype]

b) The table currently has values in this field that we need to maintain (3,4). So I need the sequence to skip those numbers. …

c) This also needs to work for update statements.

Attached are all the scripts I used so you can recreate the issues.

When I run an insert I get the below errors:

ORA-01403: no data found

ORA-04088: error during execution of trigger

HERE'S THE CODE:

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

-- Create the SequenceCREATE SEQUENCE MYSCHEMA.TEST_SEQ START WITH 1 MAXVALUE 13 INCREMENT BY 1 CYCLE NOCACHE NOORDER;-- Create the TableCREATE TABLE MYSCHEMA.TEST_SEQ (item nvarchar2(2) NOT NULL,seq nvarchar2(6) NULL)TABLESPACEUSERSSTORAGE (initial 50k);--Insert rows into the tableINSERT ALLINTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('A','')INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('B','')INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('C','3')INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('D','4')INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('E','')SELECT * FROM DUAL;--Create the triggerCREATE OR REPLACE TRIGGER MYSCHEMA.TEST_TRIGBEFORE INSERT ON MYSCHEMA.TEST_SEQFOR EACH ROW    DECLARE seqto NUMBER(6);    BEGIN      IF :new.SEQ IS NULL THEN        IF MYSCHEMA.TEST_SEQ.NEXTVAL = '100000'        THEN          SELECT 104000 INTO seqto FROM DUAL;        BEGIN         EXECUTE IMMEDIATE 'ALTER SEQUENCE MYSCHEMA.TEST_SEQ RESTART START WITH seqto';         SELECT MYSCHEMA.TEST_SEQ.NEXTVAL INTO :new.SEQ FROM DUAL;        END;        END IF;        ELSE          IF MYSCHEMA.TEST_SEQ.NEXTVAL = '999999'            THEN              BEGIN                EXECUTE IMMEDIATE 'ALTER SEQUENCE MYSCHEMA.TEST_SEQ RESTART START WITH 1';                SELECT MYSCHEMA.TEST_SEQ.NEXTVAL INTO :new.SEQ FROM DUAL;                     END;          END IF;      END IF;          EXCEPTION WHEN NO_DATA_FOUND THEN      BEGIN        SELECT 1 INTO :new.SEQ FROM DUAL;        INSERT INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES (:new.ITEM,:new.SEQ);    END;    END;/--Insert test rowsINSERT ALLINTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('F','12')INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('G','')SELECT * FROM DUAL;
Tagged:
mathguyRanagalUser_I4Y3C

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,470 Red Diamond
    edited Jun 24, 2020 8:18AM Answer ✓

    Actually, there is a chance 3/4/ will not be skipped. Assume SEQ.NEXTVAL = 4. Then code I posted generates another SEQ.NEXTVAL. Issue is some other sessions could generate SEQ.NEXTVAL too, so we can't assume our session will get SEQ.NEXTVAL=5. It can get 3 or 4. We need to loop until we get anything but 3 or 4:

    DROP SEQUENCE SEQ

    /

    DROP TABLE TBL PURGE

    /

    CREATE SEQUENCE SEQ

      MAXVALUE 10

      CYCLE

      CACHE 5

    /

    CREATE TABLE TBL(

                    COL NUMBER

                    )

    /

    CREATE OR REPLACE

      TRIGGER TBL_BIR

        BEFORE INSERT

        ON TBL

        FOR EACH ROW

        BEGIN

            :NEW.COL := SEQ.NEXTVAL;

            WHILE :NEW.COL IN (3,4) LOOP

              :NEW.COL := SEQ.NEXTVAL;

            END LOOP;

    END;

    /

    INSERT

      INTO TBL

      SELECT  1

        FROM  DUAL

        CONNECT BY LEVEL <= 15

    /

    SELECT  *

      FROM  TBL

    /

          COL

    ----------

            1

            2

            5

            6

            7

            8

            9

            10

            1

            2

            5

            6

            7

            8

            9

    15 rows selected.

    SQL>

    SY.

    User_I4Y3C
«13

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,047 Red Diamond
    edited Jun 23, 2020 12:31PM

    Hi,

    4249458 wrote:...Attached are all the scripts I used so you can recreate the issues....

    Post everything right in your message.  Not everyone can (or will) open attachments.

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Jun 23, 2020 12:37PM
    4249458 wrote:Hello PL/SQL Experts,I need your help. … Banging my head on the wall trying to make this simple sequence trigger work. …..BACKGROUND:a) I need a sequence that will update a column with a number between (1 - 10) [the column is VARCHAR2(2) datatype]b) The table currently has values in this field that we need to maintain (3,4). So I need the sequence to skip those numbers. …c) This also needs to work for update statements.Attached are all the scripts I used so you can recreate the issues.When I run an insert I get the below errors:ORA-01403: no data foundORA-04088: error during execution of trigger

    I had a look at what you posted and stopped when I saw you issuing DDL within the trigger to alter the sequence.

    Nothing about the current code you've posted is something I'd support for real world usage. That said there are plenty of highly skilled people here that love to volunteer their time and I'm sure one of them will be able to help you with a sustainable solution.

    Can you please elaborate on your requirements, what value does a sequence between 1-10 (excluding 3 and 4) have? How can you use that for business value? Sequences aren't meant for business value, they're meant for technical value, a way to ensure a unique value in a performant manner.

    Cheers,

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,470 Red Diamond
    edited Jun 23, 2020 2:44PM

    It isn't clear what you need. Here is example of skipping sequence generated values 3 & 4:

    DROP SEQUENCE SEQ

    /

    DROP TABLE TBL PURGE

    /

    CREATE SEQUENCE SEQ

      MAXVALUE 10

      CYCLE

      CACHE 5

    /

    CREATE TABLE TBL(

                     COL NUMBER

                    )

    /

    CREATE OR REPLACE

      TRIGGER TBL_BIR

        BEFORE INSERT

        ON TBL

        FOR EACH ROW

        BEGIN

            :NEW.COL := SEQ.NEXTVAL;

            IF :NEW.COL = 3

              THEN

                :NEW.COL := SEQ.NEXTVAL;

            END IF;

            IF :NEW.COL = 4

              THEN

                :NEW.COL := SEQ.NEXTVAL;

            END IF;

    END;

    /

    INSERT

      INTO TBL

      SELECT  1

        FROM  DUAL

        CONNECT BY LEVEL <= 15

    /

    SELECT  *

      FROM  TBL

    /

           COL

    ----------

             1

             2

             5

             6

             7

             8

             9

            10

             1

             2

             5

             6

             7

             8

             9

    15 rows selected.

    SQL>

    SY.

    mathguyRanagalUser_I4Y3C
  • User_I4Y3C
    User_I4Y3C Member Posts: 16 Green Ribbon
    edited Jun 24, 2020 7:31AM

    Thanks Frank - It's been updated.

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

    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 deleted

    3) The numbers should overwrite any manual entry input by the user to the new sequence

    4) When the sequence cycles - it should still skip numbers 3 and 4

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,470 Red Diamond
    edited Jun 24, 2020 7:47AM

    I already showed how to do it.

    SY.

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

    Thank You Solomon - I like you approach. .. but I still get the below error

    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 foundORA-06512: at "MYSCHEMA.TEST_TRIG", line 2ORA-04088: error during execution of trigger 'MYSCHEMA.TEST_TRIG'
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,470 Red Diamond
    edited Jun 24, 2020 8:01AM

    Show trigger code.

    SY.

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

    I used your trigger code?!?

    CREATE OR REPLACE TRIGGER MYSCHEMA.TEST_TRIG BEFORE INSERT OR UPDATE ON MYSCHEMA.SEQ_TESTFOR 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;
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,470 Red Diamond
    edited Jun 24, 2020 8:18AM Answer ✓

    Actually, there is a chance 3/4/ will not be skipped. Assume SEQ.NEXTVAL = 4. Then code I posted generates another SEQ.NEXTVAL. Issue is some other sessions could generate SEQ.NEXTVAL too, so we can't assume our session will get SEQ.NEXTVAL=5. It can get 3 or 4. We need to loop until we get anything but 3 or 4:

    DROP SEQUENCE SEQ

    /

    DROP TABLE TBL PURGE

    /

    CREATE SEQUENCE SEQ

      MAXVALUE 10

      CYCLE

      CACHE 5

    /

    CREATE TABLE TBL(

                    COL NUMBER

                    )

    /

    CREATE OR REPLACE

      TRIGGER TBL_BIR

        BEFORE INSERT

        ON TBL

        FOR EACH ROW

        BEGIN

            :NEW.COL := SEQ.NEXTVAL;

            WHILE :NEW.COL IN (3,4) LOOP

              :NEW.COL := SEQ.NEXTVAL;

            END LOOP;

    END;

    /

    INSERT

      INTO TBL

      SELECT  1

        FROM  DUAL

        CONNECT BY LEVEL <= 15

    /

    SELECT  *

      FROM  TBL

    /

          COL

    ----------

            1

            2

            5

            6

            7

            8

            9

            10

            1

            2

            5

            6

            7

            8

            9

    15 rows selected.

    SQL>

    SY.

    User_I4Y3C