Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: reset a sequence in a trigger
Hi Werner,
Try using the below trigger . This will not give the Error . DDL Commands have implicit commit attached to it . Use of Autonomous Transaction feature will eliminate the error message. True in 8i and above .
####################
curr_val INTEGER; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0'; EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROMdual'
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment
by
-'||curr_val;
EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM
dual' INTO
curr_val;
EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' increment
by 1';
END reset_sequence;
/
#####################
Cheers
Abhishek
Received on Thu May 19 2005 - 23:07:20 CDT