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

来源：互联网发布：java短信接口短信轰炸编辑：程序博客网时间：2024/05/22 01:53

一、通过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;

0 0