Re: SQL sequence creation

Steve.Morrell_at_techprt.co.uk (Steve Morrell) wrote in message news:<3d21a3ae.0307290048.1e43705b_at_posting.google.com>...
> Hi there,
>
> I'm trying to write a piece of sql to set an Oracle sequence's nextval
> to a number specified my the max value in a set of columns. The
> sequence is populating these columns, so I want to give it a kick if
> something goes wrong so itwon't try to duplicate numbers.
>
> I'm creating the sequence with
>
> create sequence my seq
> minvalue 1
> maxvalue 999999999999999999999999999
> start with X
> increment by 1
> cache 20;
>
> and I'm getting the required vlaue by a
>
> select max(Col1) from (select Col1 from TAB1
> union
> select Col2 from TAB2
> union
> .... etc. etc. etc.)
>
> The question is how do I automatically get the single value in this
> into the X in the sequence? I tried placing the code in for the X, but
> got an ORA-01722 for it not being a proper number.
>
> Ta in advance,
> Steve.

i think you're gonna have to go with dynamic sql:

SQL> create table t1 (a number);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (2);

1 row created.

SQL> insert into t1 values (3);

1 row created.

SQL> declare
  2 x number;
  3 begin
  4 select max(a) + 1 into x from t1;
  5 execute immediate 'create sequence my_seq start with ' || x;   6 end;
  7 /

PL/SQL procedure successfully completed.

SQL> select my_seq.nextval from dual;

   NEXTVAL

         4 Received on Tue Jul 29 2003 - 20:36:31 CEST