• Home
• net.anwiba.database
• anwiba-database-oracle
• 1.0.140
• source code
• CallableResetSequenceStatement.sql

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

net.anwiba.database.oracle.utilities.CallableResetSequenceStatement.sql Maven / Gradle / Ivy

DECLARE 
  seq_name  VARCHAR2 (255) := :in1;
  current_value  INTEGER;
  BEGIN
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
    EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO current_value;
    IF current_value != 0 THEN
      IF current_value < 0 THEN
        EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY ' || current_value;
      ELSE
        EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY -' || current_value;
      END IF; 
      EXECUTE IMMEDIATE 'SELECT ' || seq_name ||'.NEXTVAL FROM dual' INTO current_value;
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1';
    END IF;
  END;