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;