Initialize sequence value

From WickyWiki
Revision as of 07:26, 5 July 2013 by Admin (talk | contribs) (3 revisions)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Increase sequence TABLE1_SEQ until above max value in table TABLE1, column ID:

CREATE OR REPLACE PROCEDURE INIT_SEQUENCE(sequencename VARCHAR2, tablename VARCHAR2, columnname VARCHAR2) is
BEGIN
	DECLARE
	  toValue NUMBER;
	  nowValue NUMBER;
	BEGIN
	  EXECUTE IMMEDIATE 'select max('||columnname||') from '||tablename into toValue;
	  EXECUTE IMMEDIATE 'select '||sequencename||'.CURRVAL from dual' into nowValue;
      LOOP
        EXIT WHEN nowValue>toValue;
	    EXECUTE IMMEDIATE 'select '||sequencename||'.NEXTVAL from dual' into nowValue;
      END LOOP;
	END;
END;
/
call INC_SEQUENCE('TABLE1_SEQ', 'TABLE1', 'ID');
SELECT TABLE1_SEQ.CURRVAL FROM DUAL;