Oracle数据库自动备份SQL文本：Procedure存储过程,View视图,Function函数,Trigger触发器,Sequence序列号等

功能：备份存储过程,视图,函数触发器,Sequence序列号等
准备工作：
--1.创建文件夹：'E:/OracleBackUp/ProcBack';--文本存放的路径
--2.执行：create or replace directory MyProcBakPath as 'E:/OracleBackUp/ProcBack';
--3.赋权限：
sqlplus /nolog
conn user/pswd as sysdba
grant select on DBA_OBJECTS to user;
--4.创建Job，自动执行，自动备份存储过程
+缩减代码,归类循环执行 lzpong 2015/03/18
+更改代码,使大存储过程也能保存,并且不会出现 "用户名"."对象名" 的格式 lzpong 2015/07/14
+更改类型定义为 long 字段类型表,使能保存大视图; 保存视图名到创建语句中 lzp 2016/05/06

+?触发器依然会有用户名的前缀?

以下是本人历经多次修改的最终Sql代码:

 CREATE OR REPLACE PROCEDURE OBJAUTOSTORE

 AS

 /* 功能：备份存储过程和视图

 准备工作：

 --1.创建文件夹 ：'E:/OracleBackUp/ProcBack';--文本存放的路径

 --2.执行：create or replace directory MyProcBakPath as 'E:/OracleBackUp/ProcBack';

 --3.赋权限：

 sqlplus /nolog

 conn nag/nag as sysdba

 grant select on DBA_OBJECTS to NAG;

 --4.创建Job，自动执行，自动备份存储过程

 +缩减代码,归类循环执行 lzpong 2015/03/18

 +更改代码,使大存储过程也能保存,并且 不会出现 "用户名"."对象名" 的格式  lzpong 2015/07/14

 +更改类型定义 为 long 字段类型表,使能保存大视图; 保存视图名到创建语句中  lzp 2016/05/06

 */

 OutFile   UTL_FILE.FILE_TYPE;

 --type user_source_table_type is table of user_source.text%TYPE INDEX BY BINARY_INTEGER;

 type user_source_table_type is table of long INDEX BY BINARY_INTEGER;

 user_source_table user_source_table_type;

 pos INTEGER;

 line integer;

 towner VARCHAR2(50) :='NAG';

 cursor abc is

   SELECT 'PROCEDURE' otype,'Proc_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql' ofile FROM DUAL

 union all SELECT 'VIEW' ,'View_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql'  FROM DUAL

 union all SELECT 'TRIGGER' , 'Trig_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql'  FROM DUAL

 union all SELECT 'SEQUENCE' , 'Sequ_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql'  FROM DUAL

 union all SELECT 'FUNCTION' , 'Func_'||to_char(sysdate, 'yyyymmddhh24miss')||'.sql'  FROM DUAL

 ;

 BEGIN

 for rec in abc loop

 dbms_output.put_line(rec.otype||','||rec.ofile);

   OutFile:=UTL_FILE.FOPEN('MYPROCBAKPATH' , rec.ofile,'w',32767);

   --for robj in (select owner,object_name from user_objects where object_type=rec.otype) loop --使用当前用户的

     for robj in (select owner,object_name from dba_objects /*user_objects*/ where object_type=rec.otype and owner=towner) loop

         --select dbms_metadata.get_ddl(rec.otype,robj.object_name,towner) into v_sql from dual;

         UTL_FILE.put_line(OutFile,'-----------------start '||robj.object_name||')----------------');

         pos:=1;

         line:=1;

         --大对象写入文件

         UTL_FILE.put(OutFile,'create or replace ');

     if(rec.otype='VIEW')then

           UTL_FILE.put(OutFile,'view '||robj.object_name||chr(10));

           execute immediate 'select text from user_views where view_name='''||robj.object_name||'''' bulk collect into user_source_table;

     elsif(rec.otype='SEQUENCE')then

           execute immediate 'select ''create sequnence ''||sequence_name||'' min_value ''||min_value||'' max_value ''||max_value||'' increment by ''||increment_by||'' start with ''||last_number||(case when cycle_flag=''Y'' then '' cycle '' else '''' end)||(case when order_flag=''Y'' then '' oreder '' else '''' end)||(case when cache_size>0 then '' cache ''||cache_size else '' nocache'' end) from user_sequences where SEQUENCE_NAME='''||robj.object_name||'''' bulk collect into user_source_table;

     else

           execute immediate 'SELECT TEXT FROM user_SOURCE WHERE name='''||robj.object_name||''' order by line ' bulk collect into user_source_table;

     end if;

         WHILE pos<=user_source_table.count LOOP

       if(line>500)then --防止 文件写入缓存满了

               dbms_output.put_line(pos||'  '||robj.object_name);

                 UTL_FILE.fflush(OutFile);

                 line:=1;

             end if;

             UTL_FILE.put(OutFile,user_source_table(pos));

       pos:=pos+1;

             line:=line+1;

         END LOOP;

     UTL_FILE.put_line(OutFile,chr(10)||'/');

         UTL_FILE.put_line(OutFile,'-----------------end   '||robj.object_name||' (line:'||user_source_table.count||'----------------');

     pos:=0;

     end loop;

   UTL_FILE.put_line(OutFile,'-----------------end of file  '||rec.ofile||'----------------',true);

     UTL_FILE.FCLOSE(OutFile);

 end loop;

 EXCEPTION

   WHEN OTHERS THEN

      UTL_FILE.put(OutFile,' pos:'||pos||chr(10)||SQLERRM||chr(10)||dbms_utility.format_error_backtrace);

      UTL_FILE.FCLOSE(OutFile);

      dbms_output.put_line(SQLERRM||chr(10)||dbms_utility.format_error_backtrace);

 END;