Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Reset the sequence

ora_1978Jul 20 2009 — edited Jul 20 2009
The 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 17 2009
Added on Jul 20 2009
1 comment
323 views