Why? That is why are you "resetting" the sequence numbers. As far as I
know you have to drop and recreate them to have them start over again.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"James Williams" <willjamu_at_mindspring.com> wrote in message
news:3ea93b37.48123183_at_nntp.mindspring.com...
> I have a bunch of sequnce numbers to reset. I would rather not drop
> and recreate them to have to do grants and recompiles.
>
> I am trying to get the below program that I wrote to work. Maybe
> someone can enlighten me on my flaw. The last number never changes.
>
> Forgive the spaghetti code!
>
> --------------------------------------------------------------------
> rem Filename: reset_seq.sql
> rem Purpose: reset a sequence to a default value if need be
> rem Date: 23-Apr-2003
> rem Invoke: invoke from sqlplus sqlplus -s / @reset_seq prefix
> seq number
> rem Example: invoke from sqlplus sqlplus -s / @reset_seq agl seq1
> 1000
>
> rem Oracle does not currently support and alter sequence parm for the
> start with
> parm.
> rem If you have a bunch of grants and procedures that depends on this
> sequence t
> his keeps you from
> rem redoing that after the drop and complete.
> rem
> --------------------------------------------------------------------
>
> set serveroutput on size 1000000
> set verify on
> define schema = '&1'
> define seq = '&2'
> define seqnbr = '&3'
>
> DECLARE
> s_sequences dba_sequences%rowtype;
> s_owner dba_sequences.sequence_owner%TYPE := '&schema';
> s_name dba_sequences.sequence_name%TYPE := '&seq';
> s_cycle dba_sequences.cycle_flag%TYPE;
> s_cache dba_sequences.cache_size%TYPE;
> mn_number dba_sequences.min_value%TYPE;
> mx_number dba_sequences.max_value%TYPE;
> s_number dba_sequences.last_number%TYPE;
> e_number dba_sequences.last_number%TYPE;
> increment dba_sequences.last_number%TYPE;
> s_seqnbr dba_sequences.last_number%TYPE := &seqnbr;
> s_seqnbr2 dba_sequences.last_number%TYPE;
> s_loop dba_sequences.last_number%TYPE;
> s_count dba_sequences.last_number%TYPE;
> s_command varchar2(100);
> s_seq_all varchar2(100);
> c_cache varchar2(30) := 'cache 20'; -- default
> c_cycle varchar2(30) := 'nocycle';
> c_maxvalue varchar2(30);
> s_phase varchar2(15);
> leave_exception exception;
> BEGIN
> -- dbms_output.put_line(' I am this far');
>
> SELECT *
> INTO s_sequences
> FROM dba_sequences
> WHERE sequence_owner = upper(s_owner) AND sequence_name =
> upper(s_name);
>
>
> IF (sqlcode != 0)
> THEN
> DBMS_OUTPUT.PUT_LINE('sqlcode='||sqlcode||' owner='||s_owner||'
> sequence='||s
> _name);
> DBMS_OUTPUT.PUT_LINE('Exit from code because sequence does not
> exist in schem
> a');
> raise leave_exception;
> END IF;
> /* -- setup for alter to trick sequence number and finally */
> s_cycle := s_sequences.cycle_flag;
> s_cache := s_sequences.cache_size;
> mn_number := s_sequences.min_value;
> mx_number := s_sequences.max_value;
> s_number := s_sequences.last_number;
> e_number := s_sequences.last_number - 1;
> s_seq_all :=
> s_sequences.sequence_owner||'.'||s_sequences.sequence_name;
> increment := s_sequences.increment_by;
> s_seqnbr2 := s_seqnbr;
>
> /* -- phase prequel start */
> s_phase := 'Phase prequel';
>
> DBMS_OUTPUT.PUT_LINE('Sequence found:
> owner='||s_sequences.sequence_owner||' seq
> uence='||s_sequences.sequence_name);
> DBMS_OUTPUT.PUT_LINE('original values');
> DBMS_OUTPUT.PUT_LINE('increment='||increment||'
> minvalue='||mn_number||' maxvalu
> e='||mx_number||' cycle='||s_cycle||' cache='||s_cache);
>
> IF s_seqnbr2 >= mx_number
> THEN
> DBMS_OUTPUT.PUT_LINE('EXIT because sequence cannot be reset greater
> than maxva
> lue');
> raise leave_exception;
> END IF;
>
> s_loop := s_seqnbr2 - 1; -- increment must be less that maxvalue -
> minvalue and
> minvalue < last_number
>
> IF s_loop >= s_number
> THEN
> DBMS_OUTPUT.PUT_LINE('EXIT minvalue cannot exceed current value');
> raise leave_exception;
> END IF;
>
> /* -- phase I update sequence temporarily */
> s_phase := 'Phase I';
> DBMS_OUTPUT.PUT_LINE('min or max cannot exceed last value');
>
> s_command := 'alter sequence '||s_seq_all||' increment by 1 minvalue
> '||s_loop |
> |' maxvalue '||s_number||' cycle nocache';
> DBMS_OUTPUT.PUT_LINE('command executed: ' || s_command);
> execute immediate s_command;
>
> IF (sqlcode != 0)
> THEN
>
>
DBMS_OUTPUT.PUT_LINE('******************************************************
*
> *****');
> DBMS_OUTPUT.PUT_LINE('*Exit from code because sequence alter failed
> phase I c
> heck*');
> DBMS_OUTPUT.PUT_LINE('*Can be restarted after correcting error
> since alter
> *');
> DBMS_OUTPUT.PUT_LINE('*did not take place
>
> *');
>
>
DBMS_OUTPUT.PUT_LINE('******************************************************
*
> *****');
> raise leave_exception;
> END IF;
>
> DBMS_OUTPUT.PUT_LINE('***********************');
> DBMS_OUTPUT.PUT_LINE('*Passed Phase I alter*');
> DBMS_OUTPUT.PUT_LINE('***********************');
>
> /* -- phase II reset start with sequence via loop */
> s_phase := 'Phase II';
>
>
> s_command := 'alter sequence '||s_seq_all||' increment by 1';
> DBMS_OUTPUT.PUT_LINE('command executed: ' || s_command);
> execute immediate s_command;
> IF (sqlcode != 0)
> THEN
>
>
DBMS_OUTPUT.PUT_LINE('******************************************************
*
> *****');
> DBMS_OUTPUT.PUT_LINE('*phase II failure alter
>
> *');
> DBMS_OUTPUT.PUT_LINE('*alter to loop did not work
>
> *');
> DBMS_OUTPUT.PUT_LINE('*check for errors
>
> *');
> DBMS_OUTPUT.PUT_LINE('*manual sequence is required
>
> *');
>
>
DBMS_OUTPUT.PUT_LINE('******************************************************
*
> *****');
> raise leave_exception;
> END IF;
>
> DBMS_OUTPUT.PUT_LINE('***********************');
> DBMS_OUTPUT.PUT_LINE('*Passed Phase II alter*');
> DBMS_OUTPUT.PUT_LINE('***********************');
>
> s_command := 'SELECT '||s_seq_all||'.'||'NEXTVAL FROM DUAL';
> DBMS_OUTPUT.PUT_LINE('command executed: ' || s_command);
> -- execute
> execute immediate s_command;
> execute immediate s_command;
>
> IF (sqlcode != 0)
> THEN
>
>
DBMS_OUTPUT.PUT_LINE('******************************************************
*
> *****');
> DBMS_OUTPUT.PUT_LINE('*phase II failure select
>
> *');
> DBMS_OUTPUT.PUT_LINE('*did not take place
>
> *');
> DBMS_OUTPUT.PUT_LINE('*check for errors
>
> *');
> DBMS_OUTPUT.PUT_LINE('*manual sequence is required
>
> *');
>
>
DBMS_OUTPUT.PUT_LINE('******************************************************
*
> *****');
> raise leave_exception;
> END IF;
>
> DBMS_OUTPUT.PUT_LINE('**************************');
> DBMS_OUTPUT.PUT_LINE('*Passed Phase II select *');
> DBMS_OUTPUT.PUT_LINE('*'||s_count||' start with*');
> DBMS_OUTPUT.PUT_LINE('**************************');
>
> /* -- phase III update sequence permanently */
> /* -- reset cache */
> s_phase := 'Phase III';
>
> IF s_cache = 0
> THEN
> c_cache := 'nocache';
> ELSE
> c_cache := 'cache '||s_cache;
> END IF;
>
> /* -- reset cycle */
>
> IF s_cycle = 'Y'
> THEN
> c_cycle := 'cycle';
> END IF;
>
> /* -- reset maxvalue */
>
> c_maxvalue := 'maxvalue '||mx_number;
> IF s_seqnbr2 = to_number('1e27')
> THEN
> c_maxvalue := 'nomaxvalue';
> END IF;
>
> s_command := 'alter sequence '||s_seq_all||' increment by
> '||increment||' minval
> ue '||mn_number||' '|| c_cycle ||' '||c_cache||' '||c_maxvalue;
> DBMS_OUTPUT.PUT_LINE('command executed: ' || s_command);
> execute immediate s_command;
>
> IF (sqlcode != 0)
> THEN
>
>
DBMS_OUTPUT.PUT_LINE('******************************************************
*
> *****');
> DBMS_OUTPUT.PUT_LINE('*Phase III failure alter
>
> *');
> DBMS_OUTPUT.PUT_LINE('*check for errors
>
> *');
> DBMS_OUTPUT.PUT_LINE('*manual sequence is required
>
> *');
>
>
DBMS_OUTPUT.PUT_LINE('******************************************************
*
> *****');
> END IF;
> DBMS_OUTPUT.PUT_LINE('Phase III has completed');
> DBMS_OUTPUT.PUT_LINE('sqlcode='||sqlcode||'
> owner='||s_sequences.sequence_owner|
> |' sequence='||s_sequences.sequence_name);
> DBMS_OUTPUT.PUT_LINE('back to original values');
> DBMS_OUTPUT.PUT_LINE('increment='||increment||'
> minvalue='||mn_number||' maxvalu
> e='||mx_number||' cycle='||c_cycle||' cache='||c_cache);
>
> /* -- exceptions */
>
> EXCEPTION
> WHEN leave_exception
> THEN
> DBMS_OUTPUT.PUT_LINE('************************************');
> DBMS_OUTPUT.PUT_LINE('Process did not reach end of job');
> DBMS_OUTPUT.PUT_LINE('Stopped in '||s_phase);
> DBMS_OUTPUT.PUT_LINE('SQLCODE -- ' || sqlcode);
> DBMS_OUTPUT.PUT_LINE('SQLERRM -- ' || sqlerrm);
> DBMS_OUTPUT.PUT_LINE('************************************');
> WHEN no_data_found
> THEN
> DBMS_OUTPUT.PUT_LINE('*****************************************');
> DBMS_OUTPUT.PUT_LINE('sqlcode='||sqlcode||' owner='||s_owner||'
> sequence='||
> s_name);
> DBMS_OUTPUT.PUT_LINE('*Exit sequence does not exist in schema*');
> DBMS_OUTPUT.PUT_LINE('* no data *');
> DBMS_OUTPUT.PUT_LINE('*****************************************');
> WHEN OTHERS
> THEN
> DBMS_OUTPUT.PUT_LINE('************************************');
> DBMS_OUTPUT.PUT_LINE('Below error noted program exiting*');
> DBMS_OUTPUT.PUT_LINE('Stopped in '||s_phase );
> DBMS_OUTPUT.PUT_LINE('SQLCODE -- ' || sqlcode );
> DBMS_OUTPUT.PUT_LINE('SQLERRM -- ' || sqlerrm);
> DBMS_OUTPUT.PUT_LINE('************************************');
> END;
> /
> set verify on
> undefine schema
> undefine seq
> undefine seqnbr
Received on Fri Apr 25 2003 - 09:18:32 CDT
