在Oracle 11g中重置序列

问题描述

我正在尝试在oracle 11g中使用以下过程将序列动态重置为1.

I am trying to reset the sequence to 1 dynamically using the following procedure in oracle 11g.

create or replace
procedure reset_sequence(p_seq in varchar2)
is
    l_value number;
begin

-- Select the next value of the sequence

    execute immediate
    'select ' || p_seq ||
    '.nextval from dual' INTO l_value;

-- Set a negative increment for the sequence,
-- with value = the current value of the sequence

    execute immediate
    'alter sequence ' || p_seq ||
    ' increment by -' || l_value || ' minvalue 0';

-- Select once from the sequence, to
-- take its current value back to 0

    execute immediate
    'select ' || p_seq ||
    '.nextval from dual' INTO l_value;

-- Set the increment back to 1

    execute immediate
    'alter sequence ' || p_seq ||
    ' increment by 1 minvalue 0';
end;
/

但是当我尝试执行重置序列 circle_seq 的过程时，出现以下错误

But when I am trying to execute the procedure to reset the sequence circle_seq, I am getting following error

BEGIN reset_sequence(vf_circle_seq); END;

                     *
ERROR at line 1:
ORA-06550: line 1, column 22:
PLS-00357: Table,View Or Sequence reference 'CIRCLE_SEQ' not allowed
           in this context
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

代码有什么问题?这在oracle 11g中是不可能的，因为我在oracle 10g中使用的相同代码之前还不能正常工作.

what is wrong in code? is this is not possible in oracle 11g because same code I used in oracle 10g before it worked fine.

推荐答案

序列名称应作为字符串传递

The sequence name should be passed as a string

BEGIN
  reset_sequence('vf_circle_seq');
END;

这篇关于在Oracle 11g中重置序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！