创建 directory
Create or replace directory FILEPATH AS 'c:/temp/';
create or replace procedure SP_WRITEFILE is
Out_file UTL_FILE.file_type;
protext varchar2(4000);
proname varchar2(30);
oldproname varchar2(30);
cursor mycursor is select b.name,b.text from DBA_OBJECTS a ,DBA_SOURCE b where a.object_name = b.name and a.object_type='PROCEDURE' and a.owner='SYS' order by b.name,b.line;
begin
open mycursor;
oldproname:=' ';
loop
fetch mycursor into proname,protext;
if (proname!=oldproname )then
if(oldproname !=' ') then
UTL_FILE.fclose(Out_file);
end if;
Out_file:=UTL_FILE.fopen('FILEPATH',proname||'.sql','W');
end if;
UTL_FILE.putf(Out_file,protext);
oldproname:=proname;
exit when mycursor%notfound;
end loop;
UTL_FILE.fclose(Out_file);
return;