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 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;