Reset the sequence
ora_1978Jul 20 2009 — edited Jul 20 2009The below procedure will reset my sequence .
CREATE OR REPLACE procedure reset_seq( p_seq_name in varchar2 )
is
l_val number;
begin
execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_val;
execute immediate
'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
' minvalue 0';
execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_val;
execute immediate
'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/
I need to call this procedure from my trigger.
When I try to call the procedure from my trigger , i am getting the error like
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: Statement ignored
2/11 PLS-00357: Table,View Or Sequence reference 'SEQ_AUD_UPD' not
allowed in this context
My trigger is as mentioned below:
create or replace trigger aud_UPD#emp
after update on emp
for each row
begin
reset_seq(seq_aud_upd);
audit_upd_pkg.ins_aud_mast( 'emp', 'ENO', :new.ENO, :old.ENO);
audit_upd_pkg.check_val( 'emp', 'ENO', :new.ENO, :old.ENO);
audit_upd_pkg.check_val( 'emp', 'ENAME', :new.ENAME, :old.ENAME);
end;
/
How to call the procedure that reset my sequence from my trigger.
thanks,
Vinodh