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

[Alan Mills]

"Steve Morrell" <St***********@techprt.co.uk> wrote in message
news:3d**************************@posting.google.c om...

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.