Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Sequence Trigger (exclude number range) Fails wtih ORA-01403: no data found
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; 
Best 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.
Answers
-
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.
-
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,
-
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.
-
Thanks Frank - It's been updated.
-
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
-
I already showed how to do it.
SY.
-
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'
-
Show trigger code.
SY.
-
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;
-
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.
