Re: Updating sequences .. WAS ddl plsql can someone spot the error

From: Frans H. <fransh_at_hotmail.com>
Date: 7 May 2003 02:59:30 -0700
Message-ID: <31abf0e5.0305070159.326d842d@posting.google.com>

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EB899AB.E4512CC8_at_exxesolutions.com>...
> Jeff Kish wrote:
>
> > Can someone recommend a way to update some sequences via a script?
> >
> > I have a big set of schemas (how have some sequences that need setting), and I'd like to run a script for each schema
> > that
> >
> > - looks at the max for a table.column
> > - sets a sequence number I am using to populate this column to one greater
> >
> > This is done for a set of about 10 table/sequence number pairs.
> >
> > I tried plsql, howver I had problems mentioned previously, and I thought maybe I was going through overkill.
> >
> > Thanks
> > Jeff Kish

Jeff,
you can take this procedure as a starting point: (with thanks to jonathan lewis' site
(http://www.jlcomp.demon.co.uk/faq/ind_faq.html), where I found the original procedure)

CREATE OR REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2

                                            ,startvalue in integer) as
	curr_val INTEGER;
	string   varchar2(25);
BEGIN
	EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';
	EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual'
INTO curr_val;
	curr_val := curr_val - startvalue + 1;
	if curr_val < 0 
	then 
	  string := ' increment by ';
      curr_val:= abs(curr_val); 
	else
	  string := '  increment by -';
 	end if;   
	
	EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||string||curr_val
;
	EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM dual'
INTO curr_val;
	EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment by

1';
END reset_sequence;
/

hth

Frans H.

>
> You must use native dynamic SQL or the DBMS_SQL package. This is DDL and can not be done with static PL/SQL.
>
> Go to http://tahiti.oracle.com and look up "EXECUTE IMMEDIATE."
Received on Wed May 07 2003 - 04:59:30 CDT