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;
相关文章