Re: PLSQL loop

From: Ian Ledzion <ian.ledzion_at_lgxbow.com>
Date: Thu, 10 May 2001 09:22:13 +0200
Message-ID: <9ddfjp$5bd$1@rex.ip-plus.net>

Problem with this is it may take ages to run if you've got 220 sequences with large numbers. Better to get the last value from the old DB and drop and recreate it in the new one. If you subsititute your schema name for <SCHEMA TO TREAT>, this script should work - well it does under 8.1.5, not sure about 7.3 ;-)

/* BEGIN SCRIPT */ DEFINE SUBST_OWNER = '<SCHEMA TO TREAT>' /* Replace with schema name */

SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ';' FROM sys.all_sequences
WHERE sequence_owner = '&&SUBST_OWNER' /

SELECT 'CREATE SEQUENCE ' || sequence_owner || '.' || sequence_name || ' ' ||

            'MINVALUE ' || min_value || ' ' ||
            'MAXVALUE ' || max_value || ' ' ||
            'INCREMENT BY ' || increment_by || ' ' ||
            DECODE (cycle_flag, 'Y', 'CYCLE', 'NOCYCLE') || ' ' ||
            DECODE (order_flag, 'Y', 'ORDER', 'NOORDER') || ' ' ||
            DECODE (cache_size, 0, 'NOCACHE', 'CACHE ' || cache_size) || ' '
||
            'START WITH ' || last_number || ';'
FROM     sys.all_sequences

WHERE sequence_owner = '&&SUBST_OWNER' /

/* END SCRIPT */ "Frank" <franjoe_at_frisurf.no> wrote in message news:fcjK6.5993$Ty6.138871_at_news1.oke.nextra.no...
> Hi!
>
> You could make a little PLSQL with a loop that picks sequence_n.NEXTVAL
> until some reasonalble condition is reached.
> If the exact value of the sequences are of interest (I hope not) creating
 a
> script based on data dictionary/current values in the old database may be
> possible. Sadly not quite a "newbie task", but I haven't time to write one
> right now :-( If questionee post a request for it, I can give it a try
> tomorrow.
>
> Frank,
>
>
>
>
> Robin Boscia <rboscia_at_att.com> wrote in message
> news:9da0u8$2k13_at_kcweb01.netnews.att.com...
> > I am a newbie
> >
> > We are doing an export and an import, upgrading from 7.3.4 to 8.1.6,
 also
> > upgrading the O.S. and moving all the data to a new machine.
> >
> > We have everything up and working, except our sequences.
> >
> > We can see them and they look the same as on the old machine, however
 when
> > we try to open a new ticket, it does not increment properly. It starts
 back
> > at number 1.
> >
> > We have over 220 sequences and do not really want to drop and recreate
 all
> > of them.
> >
> > Is there an easy way to get your sequences "working"? without going thru
> > massive amounts of scripts?
> > Can you put something in the export or the import file to increment
> > properly?
> >
> >
> > Also when you query your sequences, (we did not build the database or
 the
> > tables), we can see the sequence and it's associated table, but how do
 you
> > know what column that sequence would apply to (for incrementation )?
> >
> >
> > Thanks
> > Robin
> >
> >
>
>
Received on Thu May 10 2001 - 02:22:13 CDT