oracle自动导出Function、procedure等存储过程；

oracle自动导出Function、procedure等存储过程；

2011-02-10 11:15 1312人阅读 评论(1) 收藏 举报

function 存储 oracle object file user

1.创建directory

create or replace directory BCCCGC
  as 'd:/ccgc';

grant read,write on directory bcccgc to rhio;

2.在D盘创建一个文件夹ccgc

3.执行下列存储过程。只需要把红色部分改为'function'就是导入函数了。

 

CREATE OR REPLACE Procedure 生成procedure(ls_tablespace Varchar2) Is
  File_Handle Utl_File.File_Type;
  Stor_Text   Varchar2(4000);
  n           Number;
  i           Number;
Begin
 
 
  For a In (Select object_Name As Name From User_Objects where Object_type= Upper('procedure') ) Loop
 File_Handle := UTL_FILE.FOPEN('BCCCGC', a.name || '.txt', 'W');
 Select Max(Line) Into n From All_Source Where Owner = Upper( ls_tablespace ) And Name = a.name;
  While i <= n Loop
    Select Text Into Stor_Text From All_Source Where Owner = Upper( ls_tablespace ) And Name = a.name And Line = i;
    i := i + 1;
  Utl_File.Put_Line(File_Handle, Stor_Text);
  End Loop;
i := 1;
  Utl_File.Fclose(File_Handle);
 
  Commit;
  End Loop ;
End 生成procedure;