The the knowledge I learned when worked on the oracle data migration - Part 1

#1 Stored procedure to rest the 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;
/

#2 How to reset table and the sequence
create or replace procedure reset_table( tablename in varchar2 , sequenceName in varchar2)
is
deleteStatement varchar2(2000);
resetSeqStatement varchar2(2000);
begin
    deleteStatement := 'DELETE FROM ' || tablename;
   
     execute IMMEDIATE deleteStatement;
    declare
   
    begin
      RESET_SEQ(sequenceName);
    end;

end;
/

#3 For loops

declare
localYear NUMBER;
begin
FOR y in 1981..2013 loop
  localYear :=y ;
select localYear||'/'||(localYear+1)||'FY',localYear||'/'||(localYear+1)||'FY',2,TO_DATE(localYear||'/04/01','YYYY/MM/DD'),TO_DATE((localYear+1)||'/03/31','YYYY/MM/DD')) from dual ;
end loop;
end;


#4 case when in Join statement
INNER JOIN table1 ON table1.TYPE_CDE= (CASE WHEN xxx
THEN  xxx ELSE xxx END )

#5 Check a string included number
 LENGTH(TRIM(TRANSLATE(xxx,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ' ')))
: iS NULL  all Alphebets
else included Numbers