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

On 7 May 2003 02:59:30 -0700, fransh_at_hotmail.com (Frans H.) wrote:

>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."
Thanks. I used some pieces from this and an email reply I received, and seem to have gotten the issue addressed.

Jeff Received on Thu May 08 2003 - 13:20:10 CDT