Re: a tricky sequence object question

From: <dav1dbr0wn_at_my-deja.com>
Date: Tue, 12 Dec 2000 14:25:45 GMT
Message-ID: <915ch2$d8f$1@nnrp1.deja.com>

I ran into the same problem. I created a PL/SQL block to initialize all of my sequences. Below is an example:

DECLARE

  v_LastID                 NUMBER(10);
  v_sql                    VARCHAR2(500);

BEGIN
-- AccountID Sequence
  SELECT (MAX(accountid)+1)
    INTO v_LastID
    FROM account;
  IF v_LastID IS NULL THEN v_LastID := 1; END IF;

  v_sql := 'CREATE SEQUENCE ACCOUNTID_SEQ ' ||
           'START WITH ' || v_LastID ||
           'INCREMENT BY 1 CACHE 20';

  EXECUTE IMMEDIATE v_sql;
-- EmailID Sequence
  SELECT (MAX(Emailid)+1)
    INTO v_LastID
    FROM Email;
  IF v_LastID IS NULL THEN v_LastID := 1; END IF;

  v_sql := 'CREATE SEQUENCE EMAILID_SEQ ' ||
           'START WITH ' || v_LastID ||
           'INCREMENT BY 1 CACHE 20';

  EXECUTE IMMEDIATE v_sql;
END;
/
COMMIT
/

In article <90m7vg$rm2$1_at_nnrp1.deja.com>,   faheemrao_at_my-deja.com wrote:
> Hi all
>
> Any body know that how to create the sequence object in oracle in the
> way I want ???
>
> I want a sequence object whose start with clauses is generic , I mean
> that I can select start with clause from a query ,
> following example could give you more better idea
>
> create sequence ss
> increment by 1
> start with (select max(id) from table_name)
>
> That is all I want.
>
> any help would be appreciated :)))
>
> Faheem Rao
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 12 2000 - 08:25:45 CST