存储过程和函数被删除后恢复方法解决办法

存储过程和函数被删除后恢复方法
昨天在网上看到有人问存储过程和函数被删除后没备份怎么恢复，结合网上找到的资料，编写了一个函数
只要在数据库未重启前用sys用户进行恢复即可，执行此函数会得到重建过程或函数的语句。

SQL code

CREATE 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;