Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

User_I4Y3CJun 23 2020 — edited Jun 24 2020

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 Sequence

CREATE SEQUENCE MYSCHEMA.TEST_SEQ START WITH 1 MAXVALUE 13 INCREMENT BY 1 CYCLE NOCACHE NOORDER;

-- Create the Table

CREATE TABLE MYSCHEMA.TEST_SEQ (

item nvarchar2(2) NOT NULL,

seq nvarchar2(6) NULL)

TABLESPACE

USERS

STORAGE (initial 50k);

--Insert rows into the table

INSERT ALL

INTO 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 trigger

CREATE OR REPLACE TRIGGER MYSCHEMA.TEST_TRIG

BEFORE INSERT ON MYSCHEMA.TEST_SEQ

FOR 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 rows

INSERT ALL

INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('F','12')

INTO MYSCHEMA.SEQ_TEST (item, seq) VALUES ('G','')

SELECT * FROM DUAL;

This post has been answered by Solomon Yakobson on Jun 24 2020
Jump to Answer

Comments

Takashi Matsuoka
コマンドプロンプトからですと、下記の指定で接続可能です。

C:\>sqlplus scott/\"tig@r\"@x.x.x.x/x
904909
御回答ありがとうございます。
EXPコマンドも、御教授いただいた方法で実行できました。
1 - 2

Post Details

Added on Jun 23 2020
21 comments
1,317 views