SQL sequence creation

[Steve Morrell]

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 999999999999999 999999999999
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.

[Alan Mills]

Re: SQL sequence creation


"Steve Morrell" <Steve.Morrell@ techprt.co.ukwr ote in message
news:3d21a3ae.0 307290048.1e437 05b@posting.goo gle.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 999999999999999 999999999999
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.