Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: reset a sequence in a trigger

Re: reset a sequence in a trigger

From: Akp <abhishek1999_at_gmail.com>
Date: 19 May 2005 21:07:20 -0700
Message-ID: <1116562040.070571.148830@z14g2000cwz.googlegroups.com>


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 .

####################

CREATE OR REPLACE PROCEDURE reset_sequence (sequencename IN VARCHAR2) as
        curr_val INTEGER;
        PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
     EXECUTE IMMEDIATE 'alter sequence ' ||sequencename||' MINVALUE 0';

        EXECUTE IMMEDIATE 'SELECT ' ||sequencename ||'.nextval FROM
dual'
INTO curr_val;

        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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US