通过oracle job 调用存储过程 重置seq

通过oracle job 调用存储过程 重置seq

create or replace procedure seq_reset as n number(10);
tsql varchar2(100);
begin
execute immediate ‘select SEQ_GLOBAL.nextval from dual’ into n;
n:=-n;
tsql:='alter sequence SEQ_GLOBAL increment by '|| n;
execute immediate tsql;
execute immediate ‘select SEQ_GLOBAL.nextval from dual’ into n;
tsql:=‘alter sequence SEQ_GLOBAL increment by 1’;
execute immediate tsql;
end seq_reset;
/

declare
job1 integer;
begin
sys.dbms_job.submit(job1 ,
‘seq_reset;’,
to_date(‘20150101 00:00:00’, ‘yyyymmdd hh24:mi:ss’), – 计划的初次执行日期，请根据具体情况设定
‘sysdate+1’);
commit;
end;