How can I alter a sequence in dynamic SQL?

Question

I'm trying to create a script to migrate data from one DB to another. One thing I'm not currently able to do is set the nextval of a sequence to the nextval of a sequence in another DB.

I got the difference in values from user_sequences and generated the following dynamic SQL statements:

execute immediate 'alter sequence myseq increment by 100';
execute immediate 'select myseq.nextval from dual';
execute immediate 'alter sequence myseq increment by 1';

commit;

But nothing happens. What am I missing? If I run the same statements outside the procedure, they work fine:

alter sequence myseq increment by 100;
select myseq.nextval from dual;
alter sequence myseq increment by 1;

commit;

EDIT: Apologies to all for not being clear. I'm actually altering the sequence in the same DB. I'm only getting the value to be set from a remote DB. Perhaps it was unnecessary to mention the remote DB as it doesn't affect things. I only mentioned it to explain what my goals were.

Step 1. I get the nextval of the sequence from a remote DB.

select (select last_number
        from dba_sequences@remoteDB
        where upper(sequence_name) = upper(v_sequence_name)) - (select last_number
                                                                from user_sequences
                                                                where upper(sequence_name) = upper(v_sequence_name)) increment_by
from dual;    

Step 2. I generate dynamic SQL statements with this value:

execute immediate 'alter sequence myseq increment by 100';
execute immediate 'select myseq.nextval from dual';
execute immediate 'alter sequence myseq increment by 1';

commit;

No error was raised, but nothing happened. When I wrote the SQL statements with DBMS_OUTPUT.PUT_LINE and ran them outside they worked.

Excretion

@[APC] Yes, I don't get any error message (errors are logged). Sorry I was not clear before - I'm actually altering the sequence from the same DB that owns it. The PL/SQL procedure completes successfully. I'm using 10g 10.2.0.5.0 - 64bi – Excretion

Hmm I just tried it without dynamic SQL - it won't me put any alter statements inside the PL/SQL block, unless it's dynamic SQL. It says "Encountered the symbol "ALTER" when expecting one of the following...". My intention is to have a procedure I can run to refresh data when I need it. And I will need to have a PL/SQL block to get the value from the remote DB - it won't work with a subquery to get the value for "alter sequence myseq increment by X": – Excretion

@[APC] Hmm I just tried it without dynamic SQL - it won't me put any alter statements inside the PL/SQL block, unless it's dynamic SQL. It says "Encountered the symbol "ALTER" when expecting one of the following...". My intention is to have a procedure I can run to refresh data when I need it. And I will need to have a PL/SQL block to get the value from the remote DB - it won't work with a subquery to get the value for "alter sequence myseq increment by X". – Excretion

Answer 1

Here is some code which dynamically sets a sequence to a new (higher) value. I have written this so it will work for any sequence in your schema.

create or replace procedure resync_seq
    (p_seq_name in user_sequences.sequence_name%type)
is
    local_val pls_integer;
    remote_val pls_integer;
    diff pls_integer;
begin
    execute immediate 'select '|| p_seq_name ||'.nextval from dual'
           into local_val;
    select last_number into remote_val
    from user_sequences@remote_db
    where sequence_name = p_seq_name ;
    diff := remote_val - local_val;

    if diff > 0
    then
        execute immediate 'alter sequence  '|| p_seq_name ||' increment by ' ||to_char(diff);
        execute immediate 'select '|| p_seq_name ||'.nextval from dual'
           into local_val;
        execute immediate 'alter sequence  '|| p_seq_name ||' increment by 1';
    end if;

end;

The procedure doesn't need a COMMIT because DDL statements issue an implicit commit (two in fact).

You can execute it and see the synced value like this (in SQL*PLus):

exec resync_seq('MYSEQ')
select myseq.currval
from dual

Incidentally, the only way to reset a sequence (to its original starting value or a different lower value) is dropping and re-creating the sequence.

---

In 18c Oracle added a RESTART capability to ALTER SEQUENCE. The straightforward option ...

alter sequence myseq restart;

...resets the sequence to the value specified by the START WITH clause in the original CREATE SEQUENCE statement. The other option allows us to specify a new starting point:

alter sequence myseq restart start with 23000;

Excitingly this new starting point can be ahead or behind the current value (within the usual bounds of a sequence).

The one snag is that this new capability is undocumented (only for Oracle's internal usage) and so we're not supposed use it. Still true in 20c. The only approved mechanism for changing a sequence's value is what I outlined above.

Housebroken

+1 for a great answer. Sorry I was not clear before:( I'm actually trying to alter a sequence in the same DB. – Excretion

@[APC]Thanks, man! It finally worked! I wish I could give you +10! – Excretion

Answer 2

I wasn't quite able to understand what you mean, but here is a wild guess:

I don't see it in your code, but you're talking about executing DDL (CREATE, ALTER etc.) on another database, so I assume you are using Database Links. It is not possible to use Database Links to execute DDL on another database. You might want to consider that.

---

After the information you provided, this might be what you need. And if you want to set the current value of the sequence, you can't, according to this documentation, you need to drop/create:

declare
  ln_lastNumber DBA_SEQUENCES.LAST_NUMBER%type;
  lv_sequenceName DBA_SEQUENCES.SEQUENCE_NAME%type := 'MYSEQ';
begin
  select LAST_NUMBER
  into ln_lastNumber
  from DBA_SEQUENCES--or @remote_db;
  where
    --Your predicates;

  execute immediate 'drop sequence ' || lv_sequenceName;
  execute immediate 'create sequence ' || lv_sequenceName || ' starts with ' || ln_lastNumber;
exception
  when no_data_found then
    dbms_output.put_line('No sequence found!'); -- Or log somehow.
    raise;
  when others then
    raise;
end;

Copper

Sorry I was not clear before:( I'm actually trying to alter a sequence in the same DB. – Excretion

Answer 3

Also, DDL in dynamic SQL pacakges requires

AUTHID CURRENT_USER

when declaring the package specification, if you want it to have the credentials of the current user

Ossify

Answer 4

I took the code provided by APC and modified as below:

create or replace procedure resync_seq
    (p_seq_name in user_sequences.sequence_name%type, 
     p_table_name in user_tables.table_name%type, 
     p_pk in user_cons_columns.column_name%type)
is
     local_val pls_integer;
     remote_val pls_integer;
     diff pls_integer;
begin
      execute immediate 'select '|| p_seq_name ||'.nextval from dual'
       into local_val;

       execute immediate 'select max('||p_pk||') from '||p_table_name ||' ' 
       into remote_val ;

       diff := remote_val - local_val;

       if diff > 0
          then
          execute immediate 'alter sequence  '|| p_seq_name ||' increment by ' ||to_char(diff);
          execute immediate 'select '|| p_seq_name ||'.nextval from dual'
       into local_val;
          execute immediate 'alter sequence  '|| p_seq_name ||' increment by 1';
       end if;

 end;

Breen