By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,321 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,321 IT Pros & Developers. It's quick & easy.

SQL sequence creation

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"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.
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.