我怎样才能改变动态SQL序列？(How can I alter a sequence in dyna

我试图创建一个脚本来从一个数据库迁移到另一个数据。有一两件事，我现在不能做的是设置NEXTVAL序列到另一个数据库序列的NEXTVAL。

我得到的值差异从USER_SEQUENCES并生成以下动态SQL语句：

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

commit;

但没有任何反应。我在想什么？如果我运行的程序以外相同的语句，它们很好地工作：

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

commit;

编辑：向大家致歉对于没有说清楚。实际上，我改变在同一个数据库的序列。我只获取值从远程数据库进行设置。也许是不必要提的远程数据库，因为它不影响事情。我只提到它来解释我的目标是。

第1步。我得到的序列NEXTVAL从远程数据库。

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;

第2步：我生成这个值的动态SQL语句：

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

commit;

没有错误有人提出，但什么都没有发生。当我写了DBMS_OUTPUT.PUT_LINE的SQL语句，并把它们放到外面他们的工作。

Answer 1:

现在，你必须正确地解释你的要求，这里是一些代码。我写了这个所以它会在你的模式中的任何序列工作。

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;

该过程不需要COMMIT因为DDL语句发出一个隐含的承诺（实际上是两个）。

你可以执行它，看到了同步值这样的（在SQL * Plus）：

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

Answer 2:

我是不是很能明白你的意思，但在这里是一种野生的猜测：

我没有看到它在你的代码，但你在谈论执行DDL（ CREATE ， ALTER在另一个数据库等），所以我假设你使用的数据库链接。 这是不可能的使用数据库链接到另一个数据库中执行DDL。你可能要考虑这一点。

您提供的信息后，这可能是你所需要的。如果你想设置序列的当前值， 你不能 ，根据这个文件，你需要删除/创建：

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;

Answer 3:

此外，DDL动态SQL pacakges要求

AUTHID CURRENT_USER

声明包规范时，如果你想拥有当前用户的凭据

Answer 4:

我把APC提供并修改代码如下：

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;

文章来源: How can I alter a sequence in dynamic SQL?