CREATE OR REPLACE PROCEDURE RESET_SEQUENCE(
v_SeqName IN VARCHAR2,
v_sqlcode OUT NUMBER,
v_sqlerrm OUT VARCHAR2)
IS
Num NUMBER();
SQL_STR VARCHAR2();
BEGIN
v_sqlcode := ;
v_sqlerrm := 'successful'; EXECUTE IMMEDIATE 'SELECT ' || v_SeqName || '.NEXTVAL FROM dual ' INTO Num;
Num := - (Num); SQL_STR := 'alter sequence '|| v_SeqName ||' increment by ' || Num ;
EXECUTE IMMEDIATE SQL_STR; EXECUTE IMMEDIATE 'SELECT ' || v_SeqName || '.NEXTVAL FROM dual ' INTO Num;
SQL_STR := 'alter sequence '|| v_SeqName ||' increment by 1';
EXECUTE IMMEDIATE SQL_STR;EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
END;
注意空格,影响调用失败。