Moves an Oracle sequence number to the desired value.

setseq.sql

SET FEEDBACK OFF;
SET SERVEROUTPUT ON SIZE 1000000;
set verify off
set trimspool on
set linesize 4000

ACCEPT seq_name CHAR PROMPT 'Sequence name: ';
ACCEPT seq_value NUMBER PROMPT 'Value: ';

declare 

	l_val pls_integer;
   l_inc pls_integer;
   
begin

	SELECT &seq_value - &seq_name..NEXTVAL
	  into l_inc
	  FROM dual;

	if l_inc != 0 then
   
		execute immediate 'ALTER SEQUENCE &seq_name INCREMENT BY ' || to_char(l_inc);

		SELECT &seq_name..NEXTVAL
		  into l_val
		  FROM dual;

		execute immediate 'ALTER SEQUENCE &seq_name INCREMENT BY 1';
      
   end if;

	/*SELECT &seq_name..NEXTVAL
	  into l_val
	  FROM dual;*/

end;
/

select pas_logon_seq.currval from dual;

set verify on
SET FEEDBACK ON