Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Reseting a sequence number to value less than current last_number

Reseting a sequence number to value less than current last_number

From: James Williams <willjamu_at_mindspring.com>
Date: Fri, 25 Apr 2003 13:45:43 GMT
Message-ID: <3ea93b37.48123183@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 - 08:45:43 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US