Re: Question about Sequences

From: Raphael Ploix <raphael.ploix_at_threex.co.uk>
Date: Thu, 6 Jun 2002 11:17:31 +0100
Message-ID: <3cff36e5$0$225$ed9e5944@reading.news.pipex.net>

Ryan,

below is the bit of code I use after importing data (not through SQL loader). Look at the assumptions first, as it is used in our specific databases!
Hope this helps.

Raphael

Raphael Ploix

Principal Software Engineer

raphael.ploix_at_threex.co.uk

Three X Communication Ltd

www.threex.co.uk

assumptions:

- REC_ID is the primary key of each table (surrogate key)
- all sequence names are on the template: tableName_SQ
- the procedure can resync the sequence for a single table or all tables in

the schema (null parameter)

<package>

    procedure resyncSequence(

        p_tablename IN varchar2 default NULL); </package>

<package body>

    type t_dynsqlcur is ref cursor;

    function make_seq_name(

            p_tablename IN varchar2)
        return varchar2

    as
    begin

        return p_tablename || '_SQ';
    end make_seq_name;

    procedure trace (

        p_text IN varchar2)
    is

        pragma autonomous_transaction;
    begin

        dbms_output.put_line(p_text);
        commit;

    end trace;

    procedure resyncSequence(

        p_tablename IN varchar2 default NULL)     as

        cv t_dynsqlcur;
        v_maxrid integer;
        v_seqval integer;
        v_minseq integer;
        v_seqname varchar2(30);

        cursor c_user_tables (cp_tablename varchar2) is
            select t.table_name
                from user_tables t, user_sequences s, user_tab_columns c
                where s.sequence_name = make_seq_name(t.table_name)
                    and (cp_tablename is null
                        or t.table_name = cp_tablename)
                    and t.table_name = c.table_name
                    and c.column_name = 'REC_ID';

    begin
        dbms_application_info.set_module('<package>.resyncSequence',null);
        for cv_usertable in c_user_tables(p_tablename) loop
            v_seqname := make_seq_name(cv_usertable.table_name);
            begin
                execute immediate 'alter sequence ' || v_seqname || '

minvalue 0 increment by 1';

                open cv for 'select ' || v_seqname || '.nextval from dual';
                fetch cv into v_seqval;
                close cv;

                open cv for 'select max(rec_id) from ' ||
cv_usertable.table_name;
                fetch cv into v_maxrid;
                close cv;

                if v_maxrid is null then
                    v_maxrid := 0;
                end if;

            exception
                when others then
                    if cv%ISOPEN then
                        close cv;
                    end if;
                    raise;
            end;

            if v_maxrid <> v_seqval then
                execute immediate 'alter sequence ' || v_seqname || '
minvalue 0 increment by ' || to_char(v_maxrid - v_seqval);
                begin
                    open cv for 'select ' || v_seqname || '.nextval from
dual';
                    fetch cv into v_seqval;
                    trace('update sequence: ' || v_seqname || ' to new
value: ' || v_seqval);
                    close cv;
                exception
                    when others then
                        if cv%ISOPEN then
                            close cv;
                        end if;
                        raise;
                end;
            end if;

            execute immediate 'alter sequence ' || v_seqname || ' minvalue 0
increment by 1';
        end loop;
        dbms_application_info.set_module(null,null);
    exception
        when others then
            --pkgerr.LogError;
            raise;

    end resyncSequence;

</package body>

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0206050710.4af26dd4_at_posting.google.com...
> Someone on my project recently truncated all the tables in one of our
> databases and imported new data. The problem is that the our sequence
> generators did not reset to match the highest sequence we have in our
> primary keys, so we keep hitting a unique constraint.
>
> Is there a command to reset sequences to the highest value in the
> table?
> Id prefer to run this dynamically than have to find the highest value
> for each sequence add set it to n + 1. This would be time consuming.
>
> Thanks.
Received on Thu Jun 06 2002 - 05:17:31 CDT