SQL codeCREATE OR REPLACE FUNCTION RECOVE_PROCE(del_Time in varchar2,
proc_Name in varchar2)
RETURN VARCHAR2 IS
/**
*
* function Name :存储过程或function 删除后恢复方法
*
* del_time 对象被删除时间
*
* proc_Name :被删除对象名称
*
* return :返回重建对象的语句
*
*/
OBJ_NUM NUMBER;
str_Proc varchar2(2000);
str_End varchar2(2000) := '';
str_Sql varchar2(2000);
BEGIN
SELECT obj#
INTO OBJ_NUM
FROM obj$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
WHERE NAME = upper(proc_Name);
for i in (SELECT rowid rid, source
FROM source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
order by line) loop
select source
into str_Proc
from source$ AS OF TIMESTAMP TO_TIMESTAMP(del_Time, 'YYYY-MM-DD HH24:MI:SS')
where obj# = OBJ_NUM
AND ROWID = I.RID
order by line;
str_End := str_End || str_Proc;
end loop;
str_Sql := 'CREATE OR REPLACE ' || str_End;
return str_Sql;
exception
when others then
dbms_output.put_line(sqlcode || sqlerrm);
return null;
END RECOVE_PROCE;