oracle 备份package,批量导出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;