Add a sequence to an existing table

There are situations where a sequence needs to be added to an existing table’s primary key. In order to achieve this task, we can create a sequence dynamically getting the “START WITH” value from the primary column of the table.

e.g:
Table Name: students
Primary Column: student_no (integer)

DECLARE maxid INTEGER;
sql_stmt VARCHAR2 (200);
BEGIN
SELECT MAX(student_no)+1 INTO maxid FROM students;
sql_stmt := ‘CREATE SEQUENCE seq_students_no INCREMENT BY 1
START WITH ‘||maxid|| ‘
MINVALUE ‘||maxid||’
MAXVALUE 9999999999999999
NOCYCLE
NOORDER
CACHE 20′;
EXECUTE IMMEDIATE sql_stmt;
END;
/

Advertisement

• EXECUTE IMMEDIATE
• SEQUENCE