Re: PLSQL loop

Hi!

Agree, that is a better idea.

Frank

Ian Ledzion <ian.ledzion_at_lgxbow.com> wrote in message news:9ddfot$5c0$1_at_rex.ip-plus.net...
> 3rd try - problem with newsgoup server.
>
> The problem with looping through your sequence is that it may take a long
> time. The following script will generate a script to drop and recreate
> sequences with the correct last number. Run it on your old DB, copy the
> output and run that on the new one.
>
> /* 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 - 10:09:11 CDT