CODEWITHSUNDEEP
oracle-databaseplsqloracle11gsequence
x.509
x.509

Reputation: 2235

Question Regarding Oracle Sequence

We already have a sequence in the database, now I have to modify that sequence so that i start from a specific value which i will be fetching from the db. I have written following code

DECLARE
  i_Seq   INTEGER;
BEGIN
  SELECT MAX (col_id) + 1 INTO i_Seq from Table;

  EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQHDR MINVALUE '|i_Seq|' MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH '|i_Seq|' CACHE 20 NOORDER NOCYCLE'
END;
/

I have following questions.

  1. Do i have to drop old sequence first or oracle will do it by itself?
  2. The start with clause in sequence creation. Am i using it right? or i can replace it with any number?

Upvotes: 2

Views: 5548

Answers (1)

Justin Cave
Justin Cave

Reputation: 231851

Yes, you would need to drop the sequence if it already exists. There is no CREATE OR REPLACE SEQUENCE option.

Yes, you are using the START WITH clause correctly. There is no need to specify a MINVALUE since the sequence is declared NOCYCLE.

Upvotes: 7

Related Questions