Creating sequences w/ variable starting values

From: Michael Spoonauer <spoonau_at_voicenet.com>
Date: Mon, 7 Dec 1998 23:41:48 -0500
Message-ID: <y72b2.18667$Yy5.17006575_at_news2.voicenet.com>

[Quoted] I should have been more descriptive in my first message. My question is whether Oracle supports setting the starting value of a sequence using a variable that has been populated from a select statement, e.g., (pardon the pseudocode)...

DECLARE    SEQ_START NUMBER(5) := 1;    CURSOR CURSOR1 IS

      SELECT MAX(TABLE_COL)
         FROM TABLE;

BEGIN    OPEN CURSOR1;
   FETCH CURSOR1 INTO SEQ_START;
   CLOSE CURSOR1;   WHENEVER SQLERROR CONTINUE;
   DROP SEQUENCE MY_SEQ;    WHENEVER SQLERROR EXIT ROLLBACK;
   CREATE SEQUENCE MY_SEQ

     INCREMENT BY 1
     START WITH SEQ_START
     MAXVALUE 99999;

If DDL is not supported within PL/SQL blocks, can I use substitution variables or some other trick?

Thanks,
Mike Spoonauer

---

(original message follows)

[Quoted] Does anyone know if it is possible to set the starting value for a sequence I am about to drop and recreate based on the returned value from a select statement against a table or group of tables?

I'm in Oracle 7.3.x.

Thanks,
Mike Spoonauer Received on Tue Dec 08 1998 - 05:41:48 CET