批量导出oracle中的资源（PACKAGE 、PACKAGE BODY）等

一、通过sql plus 在cmd界面直接拷贝屏幕

1、登陆sql plus
进入cmd命令界面后，输入 sqlplus apps/apps@test;

2、关闭输出段信息
sql>set heading off;

sql>spool c:/abc.sql;

sql>
select t.TEXT
  from dba_source t
 where t.owner = 'APPS'
   AND T.name IN ('CUX_QC_RCV_CHECK_PKG',
                  'CUX_QC_TEST_REQ_PKG',
                  'CUX_QC_TEST_ALLO_PKG',
                  'CUX_QC_RESE_TMP_PKG',
                  'CUX_QC_TEST_REQHEADER_PKG',
                  'CUX_QC_RESULT_ENTER_PKG',
                  'CUX_QC_RESULT_CHECKED_PKG',
                  'CUX_QC_TEST_REQCIRCLE_PKG',
                  'CUX_QC_TEST_FRESH_PKG',
                  'CUX_QC_GURANTEE_PERIOD_PKG',
                  'CUX_QC_TEST_REVALUATION_PKG',
                  'CUX_QC_RESULT_PERIOD_PKG',
                  'CUX_QC_TEST_REQPERIOD_PKG',
                  'CUX_QC_UNQUAL_RECORDS_PKG')
 order by t.name;


sql>spool off;

之后可以直接在c盘的abc.sql下获得屏幕打印的内容

二、通过存储过程

1、创建DIRECTORY（导出目录）

create or replace directory PROCE_DIR as '/tmp/test.sql';

   创建的用户需要有权限创建directory

--赋权语句
grant create any directory to apps;

   创建之后可通过如下语句查询是否创建成功

select * from dba_directories;

      制表符 chr(9)
      换行符 chr(10)
      回车符 chr(13)

2、创建导出存储过程（通过type可以筛选出需要的资源）

create OR REPLACE procedure loadProce(owner varchar2)
is
    type user_source_table_type is table of user_source.text%TYPE INDEX BY BINARY_INTEGER;
    user_source_table user_source_table_type;
    file_handle utl_file.file_type;
    stor_text VARCHAR2(4000);
    sql_stat varchar2(1000);
    sql_stat2 varchar2(1000);
    sql_stat3 varchar2(1000);
    nCount NUMBER;
   i NUMBER;
begin
    sql_stat:='select distinct(name) from all_source where NAME LIKE ''CUX%'' AND owner = ''' || upper(owner) || '''';
    execute immediate sql_stat bulk collect into user_source_table;
    file_handle:=utl_file.fopen('PROCE_DIR','test.sql','W');
    for j in 1..user_source_table.count loop
        i:=1;
        utl_file.put(file_handle,'------------'||j||'----------------'||chr(10));
        sql_stat2:='SELECT MAX(LINE) FROM ALL_SOURCE WHERE TYPE = ''PACKAGE'' AND OWNER=''' || upper(owner) || ''' AND NAME=''' || user_source_table(j) || '''';
        --dbms_output.put_line(sql_stat2);
        execute immediate sql_stat2 into nCount;
        WHILE i<=nCount LOOP
           sql_stat3:='SELECT TEXT FROM ALL_SOURCE WHERE TYPE = ''PACKAGE'' AND OWNER=''' || upper(owner) || ''' AND NAME=''' || user_source_table(j) || ''' and line = ' || i;
           --dbms_output.put_line(sql_stat3);
           execute immediate sql_stat3 into stor_text;
           i:=i+1;
         utl_file.put(file_handle,stor_text);
        END LOOP;
        
        
        utl_file.put(file_handle,chr(10));
        i:=1;
        sql_stat2:='SELECT MAX(LINE) FROM ALL_SOURCE WHERE TYPE = ''PACKAGE BODY'' AND OWNER=''' || upper(owner) || ''' AND NAME=''' || user_source_table(j) || '''';
        --dbms_output.put_line(sql_stat2);
        execute immediate sql_stat2 into nCount;
        WHILE i<=nCount LOOP
           sql_stat3:='SELECT TEXT FROM ALL_SOURCE WHERE TYPE = ''PACKAGE BODY'' AND OWNER=''' || upper(owner) || ''' AND NAME=''' || user_source_table(j) || ''' and line = ' || i;
           --dbms_output.put_line(sql_stat3);
           execute immediate sql_stat3 into stor_text;
           i:=i+1;
         utl_file.put(file_handle,stor_text);
        END LOOP;
        
    end loop;
    utl_file.fclose(file_handle);
    commit;
end loadProce;

3、调用，将TYPE BODY、PROCEDURE、TYPE、FUNCTION、TRIGGER、PACKAGE BODY、PACKAGE保存到对应目录的test.sql中

begin
   loadproce('apps');
end;