REM ************************************************************************
REM Filename:	alter_sequence.sql    
REM Description:
REM 	Script alters a sequence current value.
REM Comments:
REM
REM Modifications:
REM Author		Date	  Reason
REM Darryl B. Smith	09/01/97  Created
REM ************************************************************************

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 130
SET VERIFY   OFF
SET FEEDBACK OFF
SET ECHO     OFF
REM SET TRIMSPOO ON

ACCEPT owner     CHAR -
PROMPT 'Enter owner of the sequence [default: current user] : ';
ACCEPT seqname CHAR -
PROMPT 'Enter sequence name : ';
ACCEPT spoolfile CHAR -
PROMPT 'Enter spool file name [default: chgseq.log] : ';
ACCEPT adjflag CHAR -
PROMPT 'Enter A to adjust current value or S to set current value [default: S] : ';
COLUMN spf NEW_VALUE spoolfile
COLUMN increment_by NEW_VALUE i_by
COLUMN last_number NEW_VALUE l_number
SET TERMOUT OFF
SELECT 'chgseq.log' spf
FROM   dual
WHERE  '&&spoolfile' is null;
SET TERMOUT ON

spool &&spoolfile;

SELECT 	sequence_name,
	increment_by, 
	last_number
FROM   	all_sequences
WHERE  	sequence_owner = NVL(UPPER('&&owner'), user)
AND    	sequence_name = UPPER('&&seqname')
;

ACCEPT new_l_number     NUMBER -
PROMPT 'Enter value: ';

DECLARE
	adj_value		NUMBER;
	PROCEDURE dynamic_sql
	       (select_flag		BOOLEAN,
		string1 		VARCHAR2,
	        string2 		VARCHAR2,
	        string3 		VARCHAR2,
	        string4 		VARCHAR2,
		pv_sequence_owner IN 	VARCHAR2,
		pv_sequence_name  IN 	VARCHAR2)
	AS
		c1     	INTEGER;
	        r1     	INTEGER;
        	sql1   	VARCHAR2(2000);
		s_value NUMBER;
	BEGIN
		sql1 := string1||PV_sequence_owner||'.'||
                        PV_sequence_name||string2;
		c1 := DBMS_SQL.OPEN_CURSOR;
		DBMS_SQL.PARSE(c1,sql1,DBMS_SQL.NATIVE);
		IF select_flag THEN
		   DBMS_SQL.DEFINE_COLUMN(c1, 1, s_value);
		   r1 := DBMS_SQL.EXECUTE_AND_FETCH(c1);
		   DBMS_SQL.COLUMN_VALUE(c1, 1, s_value);
		END IF;
		DBMS_OUTPUT.PUT_LINE(sql1||';');
		DBMS_OUTPUT.PUT_LINE(string3||PV_sequence_owner||'.'||
       	        	             PV_sequence_name||string4);
		DBMS_SQL.CLOSE_CURSOR(c1);
	EXCEPTION
		WHEN OTHERS THEN
	        DBMS_OUTPUT.PUT_LINE(sqlerrm);
		RAISE_APPLICATION_ERROR(-20000,'Critical Error.  Exiting...');
	END;
BEGIN
	IF UPPER(NVL('&&adjflag','S')) = 'A' THEN
	   adj_value := TO_NUMBER(NVL(&&new_l_number,0));
	ELSE
	   adj_value := TO_NUMBER(NVL(&&new_l_number,0)) - TO_NUMBER(NVL('&&l_number','0'));
	END IF;
	dynamic_sql(FALSE,
		    'Alter sequence ',' increment by '||adj_value,
	            'Sequence ',' altered.',
		    NVL(UPPER('&&owner'), user), NVL(UPPER('&&seqname'), user) );
	dynamic_sql(TRUE,
		    'SELECT ','.nextval s_value FROM dual',
	            'Sequence ',' incremented.',
		    NVL(UPPER('&&owner'), user), NVL(UPPER('&&seqname'), user) );
	dynamic_sql(FALSE,
		    'Alter sequence ',' increment by '||TO_CHAR(TO_NUMBER(NVL('&&i_by','0'))),
	            'Sequence ',' altered.',
		    NVL(UPPER('&&owner'), user), NVL(UPPER('&&seqname'), user) );
EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/

SELECT 	sequence_name,
	increment_by, 
	last_number
FROM   	all_sequences
WHERE  	sequence_owner = NVL(UPPER('&&owner'), user)
AND    	sequence_name = UPPER('&&seqname')
;

SPOOL OFF;
SET FEEDBACK ON
UNDEFINE owner       
UNDEFINE seqname     
UNDEFINE spoolfile   
UNDEFINE i_by        
UNDEFINE l_number    
UNDEFINE new_l_number
UNDEFINE adjflag