Create sequence in Oracle without dropping it

by GarciaPL on Thursday, 25 August 2016

Some of you might thinking how to create sequence for table which contains some records. You might always export all records from database, then eventually create sequence and after all import those records once again into database. Nevertheless there is a solution how to create this sequence using PL/SQL.



DECLARE
  seq_value_holder        NUMBER;
BEGIN
  -- create a sequence for YOUR_TABLE
  SELECT NVL(MAX(id), 0) + 1
  INTO seq_value_holder
  FROM YOUR_TABLE;
  EXECUTE immediate 'create sequence SEQ_YOUR_TABLE start with ' || seq_value_holder || ' increment by 1';
END;