因为业务需要每月需要增加一个序列,想到了使用job定时创建,每次创建一年的。写此job的过程中遇到一些问题,在此记录一下。
第一步:写创建序列(sequence)的存储过程。代码如下:
create or replace procedure pro_create_seq(sequencePrefix in varchar2)
is
strYear varchar2(4);
strMonth varchar2(6);
strSql varchar2(500);
begin
select to_char(sysdate,'yyyy') into strYear from dual;
for i in 1..12 loop
strMonth:=strYear||lpad(i,2,'0');--组装月份,形如:201201
strSql:='create sequence '||sequencePrefix||strMonth||' start with 1 increment by 1 MINVALUE 0 MAXVALUE 99999999999 ';
execute immediate strSql;
end loop;
end pro_create_seq;
创建存储过程成功,但测试该报错了:
SQL> exec pro_create_seq('FACTORY_NOTICE_INFO_DB_');
begin pro_create_seq('FACTORY_NOTICE_INFO_DB_'); end;
ORA-01031: 权限不足
ORA-0