Re: SQL sequence creation

"Steve Morrell" <Steve.Morrell_at_techprt.co.uk> 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 doin;t think you will get the answer into your X value. IF using SQL*Plus try it this way.

SPOOL seq.sql

SELECT 'create sequence....' || max(col1) || ' increment by....' from (select col1 from tabe1.......)
/

SPOOL OFF start seq

so you have a select statement to produce your cerate sequence statement, whic is written to a file and then run automatically. Received on Tue Jul 29 2003 - 11:58:14 CEST