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

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;