Oracle : create Generic Store Procedure for data migration

I am working on data migration from the legacy db to the new db. For those lookup tables, the sequence need to start from 1 each time do the migration testing. And in addition, the existing table should be wiped out before import data from old db. So, I created to helper store procedure to implement it.


-- function or procedure for the easy for testing of data migration
-- this will be used to reset the sequence(back to 1) for each lookup table

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;

create or replace
procedure reset_lookup( tablename in varchar2 )
is
deleteStatement varchar2(2000);
resetSeqStatement varchar2(2000);
begin
    deleteStatement := 'DELETE FROM LT_' || tablename;
     
    EXECUTE IMMEDIATE deleteStatement;
 
  declare
      sequenceName varchar2(2000);
    begin
      sequenceName := 'LT_' || tablename||'_SEQ';
      RESET_SEQ(sequenceName);
    end;

end;