Home » SQL & PL/SQL » SQL & PL/SQL » using select statement wile creating sequence (Oracle 10 g)
using select statement wile creating sequence [message #279580] Fri, 09 November 2007 03:17 Go to next message
JandaR
Messages: 2
Registered: November 2007
Junior Member
Hi!
I try to change from a selfmanaged versioning system to a sequence using system. the value of the sequence is then written into a field in the table1.
So when migrating i don't want to loose my current version number so i tried to create a Sequence like this:



CREATE SEQUENCE sequence1
INCREMENT BY 1
START WITH 555
MINVALUE 1
MAXVALUE 999999999999999999999999999
NOCYCLE
ORDER
CACHE 20
/

and it works. but when i try to make it more comfortable (cause i have this issue with a lot of tables and max(version) takes 15 minutes or more. so i tried

CREATE SEQUENCE sequence1
INCREMENT BY 1
START WITH (select max(version) from table1)
MINVALUE 1
MAXVALUE 999999999999999999999999999
NOCYCLE
ORDER
CACHE 20
/

but i get invalid number...

is there another method to automatically set the start with property or do i have to create the sequences hardcoded with the max-value?

Re: using select statement wile creating sequence [message #279584 is a reply to message #279580] Fri, 09 November 2007 03:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

If those Numbaers are so vital for your application, It is bad idea to use Sequence to get the continuation during migration process.

One alternative is to impement a mechanicsm in database using a lookup table with a script to get the required Number , though is also having some drawbacks

Thumbs Up
Rajuvan.
Re: using select statement wile creating sequence [message #279585 is a reply to message #279580] Fri, 09 November 2007 04:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How are you planning to migrate your application that risks losing the current values?

You can generate the DDL to recreate the sequences in their current states using DBMS_METADATA:
select dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner)
from all_sequences;
Re: using select statement wile creating sequence [message #279586 is a reply to message #279580] Fri, 09 November 2007 04:03 Go to previous messageGo to next message
JandaR
Messages: 2
Registered: November 2007
Junior Member
those numbers are just changing when users are working with the system so i wanted to start it with my non-working automatic script during the night in which the new release is published...
Re: using select statement wile creating sequence [message #279587 is a reply to message #279580] Fri, 09 November 2007 04:06 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

We have some automated scripts that create sequences.

Within the PL/SQL package it does this

EXECUTE IMMEDIATE 'create sequence my_seq_'||v_prod_id||'_1 '||
                              'start with '||
                              v_number ||
                              ' increment by 1 '||
                              'minvalue '|| pnum_seq_start(v_prod_num)||' '||
                              'maxvalue '|| pnum_seq_end(v_prod_num)||' '||
                              'nocycle '||
                              'cache 100';


where v_number is a variable thats populated with a number at run time, so you could do a select into that?

Previous Topic: bad bind variables while creating a stored procedure
Next Topic: FMT File copied into program at Compile Time
Goto Forum:
  


Current Time: Mon Dec 05 12:57:25 CST 2016

Total time taken to generate the page: 0.17331 seconds