Re: ORACLE-SQL : Sequence Problem (Need SQL GURUS)

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Thu, 10 Sep 1998 06:57:19 +0200
Message-ID: <6t7m54$8f3$1_at_newton.a2000.nl>

> The only way I know of "resetting" a sequence is to kill it and recreate
>it.

You can use alter to set the increment_by to a negative value. See below.

> I would suggest using the 'for counter in 1 .. <p_limit> loop'

This can take a long time...

>>How can I write the code (SQL or PL/SQL or SQL*PLUS)that will increase the
>>sequence value until it match the value of other parameter value and reset
>>its value back to 1.

Can't you set max_value when you define the trigger?

I do not really understand the problem, but below is a procedure that will set any sequence to a desired value. This is not the final version I once used -- e.g. you'll have to debug and test it yourself.

Arjan.

  function exec_ddl( p_statement in varchar2)     return integer
  is
    l_cursorid integer;
    l_rowcount integer;
  begin
    l_rowcount := null;
--

    l_cursorid := dbms_sql.open_cursor;
    dbms_sql.parse( l_cursorid, p_statement, dbms_sql.native);     l_rowcount := dbms_sql.execute( l_cursorid);     dbms_sql.close_cursor( l_cursorid);
--

    if l_rowcount is null then
      raise_application_error( -20000, 'Error executing ' || p_statement);     end if;
--

    return l_rowcount;
  exception
    when others then

      if dbms_sql.is_open( l_cursorid) then
        dbms_sql.close_cursor( l_cursorid);
      end if;
      raise;

  end exec_ddl;

  --
  -- -----------------------------------------------------------------
  --

  procedure alter_session_commit( p_change in varchar2) is     l_cursor integer;
    l_aarows integer;
  begin
    l_cursor := dbms_sql.open_cursor;
    dbms_sql.parse ( l_cursor, 'alter session '|| p_change

                     || ' commit in procedure', dbms_sql.v7);

    l_aarows := dbms_sql.execute (l_cursor);     dbms_sql.close_cursor (l_cursor);
  exception
    when others then

      if dbms_sql.is_open (l_cursor) then
        dbms_sql.close_cursor (l_cursor);
      end if;
      raise;

  end alter_session_commit;

  --
  -- -----------------------------------------------------------------
  --
  -- The column last_number gives the last value of the sequence
  -- that is stored somewhere in Oracle. This value is not necessarily
  -- the value that would be returned by currval. Nor wil nextval return
  -- last_number + increment...
  -- If the cache_size is 20, and currval is 1001, then last_number
  -- yeilds 1020. As soon as nextvall returns 1020, last_number will
  -- be set to 1040. Therefore, the column last_number only tells us
  -- what the last issued sequence number was when cache_size = 1.
  -- In all other case, nextval need to be used to get a new value.
  --
  -- -----------------------------------------------------------------
  --
  function  set_seq( p_seq_name in varchar2
                   , p_newval   in integer
                   ) return integer

  is
--

    r_useq          user_sequences%rowtype;
    l_increment_by  user_sequences.increment_by%type;
    l_newval        user_sequences.last_number%type;
    l_lastval       user_sequences.last_number%type;
    l_nextval       user_sequences.last_number%type;
    l_currval       user_sequences.last_number%type;
    l_succes        binary_integer;


    --

    l_meld          varchar2(100);

--

  begin
--

    l_newval := p_newval;
--

    begin

      select *
      into   r_useq
      from   user_sequences
      where  sequence_name = upper( p_seq_name);
    exception
      when no_data_found then
        raise_application_error( -20000, 'Sequence ' || p_seq_name || ' does
not exist');
        return null;

    end;
--

    if l_newval < r_useq.min_value then
      l_newval := r_useq.min_value;
    end if;
--

    if l_newval > r_useq.max_value then
      l_newval := r_useq.max_value;
    end if;
--

    if r_useq.cache_size = 1 then

      l_currval := r_useq.last_number;
      l_lastval := l_currval;
      if l_currval = l_newval then
        return l_currval;
      end if;
    else
      --
      l_currval := exec_query( 'select ' || p_seq_name
                               || '.nextval from sys.dual');
      --
      l_lastval := l_currval - r_useq.increment_by;
      if l_currval = l_newval then
        return l_currval;
      elsif l_currval is null then
        return null;  -- in fact, error already raised by exec_query
      end if;

    end if;
--

• l_currval != l_newval
  --
  
• l_newval r_useq_increment_by l_lastval l_currval l_increment_by
• -------- ------------------- --------- --------- --------------
• 100 1 5 6 94
• 100 1 123 124 -24
• 100 10 50 60 40
  --
  l_increment_by := l_newval - l_currval;
  --
  alter_session_commit( 'ENABLE');
  --
  --
  l_succes := exec_ddl( 'alter sequence ' || p_seq_name || ' increment by ' || to_char( l_increment_by));
  --
  if l_succes is null then return null; -- in fact, error already raised by exec_ddl end if;
  --
  
• Make l_currval = l_newval
  --
  l_currval := exec_query( 'select ' || p_seq_name || '.nextval from sys.dual');
  --
  --
  l_succes := exec_ddl( 'alter sequence ' || p_seq_name || ' increment by ' || to_char( r_useq.increment_by));
  --
  if l_succes is null then return null; -- in fact, error already raised by exec_ddl end if;
  --
  alter_session_commit( 'DISABLE');
  --
  return l_currval; end set_seq; --
  • ----------------------------------------------------------------- -- procedure set_seq( p_seq_name in varchar2 , p_newval in integer) is l_dummy binary_integer; begin l_dummy := set_seq( p_seq_name, p_newval); end; -- -- end of script

Received on Thu Sep 10 1998 - 06:57:19 CEST