Title: Resetting Sequences To One

Author: Craig Coleman, a DBA and GIS Consultant for ESRI (UK) Ltd, in Watford, England.

I am often requested to "clear-down" databases for development purposes, or when copying a live database into a test
database. When this occurs, I simply import the live data and truncate all of the tables to empty them.

Sequences were my only real problem. The routine is implemented as a procedure so that it can be embedded in other
programs easily. It makes use of the very useful DBMS_SQL and DBMS_OUTPUT packages and has an example of how to
use it. To make it more available, create a public synonym for it and grant execute to DBA. The routine can easily be extended
to accept the owner of the Sequence to be reset, however, you would probably need to have ALTER ANY SEQUENCE
privilege.

create or replace procedure reset_sequences(
                pSeqName In Varchar2) Is
        --
        LastNumber Number;
        RetCode Number;
        --
        Function ExecuteSQL(Stmt In Varchar2) Return Number Is
                Handle Integer;
                RetVal Number;
                --
                Function IsQuery(pStmt In Varchar2) Return Boolean Is
                Begin
                        Return (UPPER(SUBSTR(LTRIM(pStmt),1,6)) = 'SELECT');
                End IsQuery;
        Begin
                Handle := DBMS_SQL.OPEN_CURSOR;
                DBMS_SQL.PARSE(Handle,Stmt,DBMS_SQL.NATIVE);
                IF IsQuery(Stmt) THEN
                        DBMS_SQL.DEFINE_COLUMN(Handle,1,RetVal);
                        RetVal := DBMS_SQL.EXECUTE(Handle);
                        LOOP
                                EXIT WHEN DBMS_SQL.FETCH_ROWS(Handle) = 0;
                                DBMS_SQL.COLUMN_VALUE(Handle,1,RetVal);
                        END LOOP;
                ELSE
                        RetVal := -1;
                END IF;
                DBMS_OUTPUT.PUT_LINE(Stmt);
                DBMS_SQL.CLOSE_CURSOR(Handle);
                Return(RetVal);
        Exception
                When OTHERS Then
                        If DBMS_SQL.IS_OPEN(Handle) THEN
                                DBMS_SQL.CLOSE_CURSOR(Handle);
                        End If;
                        Return -99;
        End ExecuteSQL;
        --
begin
        LastNumber := ExecuteSQL(
                'select last_number from all_sequences where sequence_name = '''||
                upper(pSeqName)||'''');
        IF LastNumber > 0 THEN
                DBMS_OUTPUT.PUT_LINE('Last Number for Sequence was:'||LastNumber);
                RetCode := ExecuteSQL(
                        'alter sequence '||pSeqName||
                        ' cycle nocache minvalue 0 maxvalue '||to_char(LastNumber));
                DBMS_OUTPUT.PUT_LINE(ExecuteSQL('select '||pSeqName||'.nextval from dual'));
                DBMS_OUTPUT.PUT_LINE(ExecuteSQL('select '||pSeqName||'.nextval from dual'));
                RetCode := ExecuteSQL(
                        'alter sequence '||pSeqName||
                        ' nocycle nocache nomaxvalue');
        ELSIF LastNumber = -1 THEN
                DBMS_OUTPUT.PUT_LINE('DDL Statement executed!!!');
        ELSIF LastNumber = -99 THEN
                DBMS_OUTPUT.PUT_LINE('Syntax error');
        END IF;
end;
/