Updating an oracle sequence via plsql
Oracle does not let you change the value of a sequence.If you need to change its value, you should re-create the sequence.
SQL CREATE or replace PROCEDURE Update Department 2 (Dept ID IN integer, 3 Department Name IN varchar2, 4 Department Description IN varchar2) 5 6 AS 7 BEGIN 8 UPDATE Department 9 SET Name = Department Name, 10 Description = Department Description 11 WHERE Department ID = Dept ID; 12 END; 13 / Procedure created.
SEQUENCE_NEWVALUE( seqowner VARCHAR2, seqname VARCHAR2, newvalue NUMBER) AS ln NUMBER; ib NUMBER; BEGIN SELECT last_number, increment_by INTO ln, ib FROM dba_sequences WHERE sequence_owner = upper(seqowner) AND sequence_name = upper(seqname); EXECUTE IMMEDIATE 'ALTER SEQUENCE '
To retrieve the next value in the sequence order, you need to use option specifies how many sequence values will be stored in memory for faster access.
The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost".