Tuesday, December 22, 2009

ORA-08004: sequence exceeds MAXVALUE and cannot be instantiated

ORA-08004: sequence exceeds MAXVALUE and cannot be instantiated

Soution:=
==============

alter sequence maxvalue ;

Sequence syntax:
---------------
CREATE SEQUENCE
INCREMENT BY
START WITH
MAXVALUE / NOMAXVALUE
MINVALUE / NOMINVALUE
CYCLE / NOCYCLE
CACHE <#> / NOCACHE
ORDER / NOORDER;


CREATE TABLE TEST (ID NUMBER, data VARCHAR2(255));


CREATE SEQUENCE seq_for_test
START WITH 1
INCREMENT BY 1
MAXVALUE 10;


CREATE OR REPLACE TRIGGER trigger_for_seq_test
BEFORE INSERT
ON TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
SELECT seq_for_test.NEXTVAL
INTO :NEW.ID
FROM DUAL;
END;


CREATE OR REPLACE TRIGGER trigger_for_seq_test
BEFORE INSERT
ON TEST
FOR EACH ROW
BEGIN
SELECT nvl(:new.id,seq_for_test.NEXTVAL)
INTO :NEW.ID
FROM DUAL;
END;



ALTER SEQUENCE seq_for_test MAXVALUE 15



Sequence in PL/sql
==================


INSERT INTO TEST (id, data)
VALUES (seq_for_test.NEXTVAL, 'halim');

INSERT INTO TEST (id, data)
VALUES (seq_for_test.CurrVAL, 'halim'); --(what happen)


SELECT 'CREATE SEQUENCE test_sequence MINVALUE 0 START WITH '||MAX(id_no)+1||' INCREMENT BY 1 CACHE 20'
INTO v_test
FROM table_name:

EXECUTE IMMEDIATE v_sql;


Sequence Related Queries
==========================
SELECT sequence_name, last_number
FROM user_sequences;

Next Number From Sequence
--------------------------

SELECT sequence_name, (last_number + increment_by) NEXT_VALUE
FROM user_sequences;

No comments: