oracle 用sql导出sys下的存储过程到文件中

创建 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;