MAXIMO 序列维护

经常在导入导出MAXIMO的时候，遇到序列值不匹配的情况，每次都客户应用或测试的时候发现这个问题，搞得巨没面子，为此，写了如下脚本，解决此问题
declare
– Local variables here
i integer;
– Local variables here
cursor seq_cursor is(
select * from maxsequence   );
seq_cursor_row seq_cursor%rowtype;
imaxseq        number(10);
icurvalseq     number(10);
strsql         varchar2(200);
ic             number;
isExist        number(10);
begin
– Test statements here
open seq_cursor;
loop
fetch seq_cursor
into seq_cursor_row;
EXIT WHEN seq_cursor%NOTFOUND;
imaxseq := 0;
strSQL  := ’select count(*) from all_objects where object_name=:1 and object_type=”TABLE”’;
execute IMMEDIATE strSQL
into isExist
using seq_cursor_row.tbname;
dbms_output.put_line(isExist);
if (isExist = 1) then
strsql := ’select max(‘ || seq_cursor_row.name || ‘)  from ‘ ||
seq_cursor_row.tbname;
EXECUTE IMMEDIATE strsql
into imaxseq;

strsql := ’select ‘ || seq_cursor_row.sequencename ||
‘.nextval from dual’;

EXECUTE IMMEDIATE strsql
into icurvalseq;

ic := imaxseq – icurvalseq;
if ic <> -1 then
strSQL := ‘alter sequence ‘ || seq_cursor_row.sequencename ||
‘ increment by ‘ || ic || ‘ nocache’;
EXECUTE IMMEDIATE strsql;
strSQL := ’select ‘ || seq_cursor_row.sequencename ||
‘.nextval from dual’;
EXECUTE IMMEDIATE strsql
into ic;
strSQL := ‘alter sequence ‘ || seq_cursor_row.sequencename ||
‘ increment by 1 cache 20′;

EXECUTE IMMEDIATE strsql;
end if;
end if;

end loop;
close seq_cursor;
end;