Creating oracle sequence using max colum value 2

[tatscub]

Hello there I hope you can help me . Im very new to oracle unfortunatley and have now managed to get my self stuck, after some serious hunting around the internet i am non the wiser. the problem i have is i would like to create a sequence in oracle using the max customer_id from an existing table as the sequence's start value , e.g

CREATE SEQUENCE customer_id_sequence
START WITH <max value for the customer_id colum in the customer table > + 1
INCREMENT BY 1

unfortunatley just poping in
START WITH (SELECT max(customer_id) FROM customer)
just produces an invalid number format error .

I ma using oracle 10 g
now im fairly sure i can do somethign with substitution variables to sort this out but i am at a loss how to proceed. I would be very greatfull if any one can point me in the right direction to get me started towards a solution

Im sorry if i have posted this in the wrong area , im new to tek-tips also and im still finsding my way around

 

[taupirho]

You can do this with a little PL/SQL. I'm on Oracle9i but it should work on 10 also.


SQL> select max(x) from tom;

MAX(X)
----------
3

SQL>

$ JAVA> type tom.sql
declare
mysql varchar2(100);
begin
select 'create sequence testseq start with ' || max(x) || ' increment by 1' into
mysql from tom;
execute immediate mysql;
end;
/
exit
/
$ sqlplus myuser/mypass @tom
SQL*Plus: Release 9.2.0.2.0 - Production on Tue Jun 16 13:04:31 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production


PL/SQL procedure successfully completed.

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning option
JServer Release 9.2.0.2.0 - Production
$ JAVA>
$ sqlplus myuser/mypass

SQL> select testseq.nextval from dual

NEXTVAL
----------
3

SQL> /

NEXTVAL
----------
4

SQL>

Hope this help or gives you some pointers




In order to understand recursion, you must first understand recursion.

 

[LKBrwnDBA]

Try this:

col max_nr new_value start_sq
SELECT max(customer_id)+1 max_nr FROM customer;
CREATE SEQUENCE customer_id_sequence 
       START WITH  &&start_sq
       INCREMENT BY 1;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tatscub]

Thankyou every one for your help its very much apreciated . Im geting there i think . a college at work has recomended i try using teh column command and a substitution variable i am going to have a play around and will post up anything i find

Thank you all again

 

[SantaMufasa]

Tatscub,

Welcome to Tek-Tips, and specifically to the Oracle forums here. We hope you find your time here beneficial.

As an new member, you might not yet be aware that when someone provides helpful information in response to a question, we not only say, "Thank you", as you have done, but we also click the link that appears in the lower left corner of the posts that you find helpful:

Thank <Member Name> for this valuable post!. This provides a

next to the helpful post, not only to thank the helpful member, but also to direct attention to the helpful post for others that follow, who have a similar need to yours.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”