存储过程或function 删除后恢复方法

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;