|
|
|
Re: How to restart sequence [message #11364 is a reply to message #11015] |
Mon, 22 March 2004 23:09 |
Kiema
Messages: 1 Registered: March 2004
|
Junior Member |
|
|
Try this Function
CREATE OR REPLACE function restart_seq(SeqName IN varchar2) return Integer
as
PRAGMA AUTONOMOUS_TRANSACTION;
vs integer;
inc integer;
junk integer;
sqName varchar2(50);
begin
sqName := seqName||'.nextval';
execute immediate 'select '||sqName||' from dual' into vs;
inc := 0 - vs;
execute immediate 'alter sequence '||SeqName||' minvalue '|| inc;
execute immediate 'alter sequence '||SeqName||' increment by '||inc;
execute immediate 'select '||sqName||' from dual' into junk;
execute immediate 'alter sequence '||SeqName||' increment by 1';
RETURN junk;
end;
/
|
|
|