参考:
http://nimishgarg.blogspot.com/2017/09/exporting-packages-procedures-functions.html

有段时间想把 Oracle Database 导出为文件,做 git 管理,方便追踪更改,搜索全文。

有时候追踪某个输出,user_source没有 view(视图),view(视图) 要去 dba_views 表去找,内容还是 long 类型,很不方便检索。

于是搜到了这个脚本。

食用方式:

  1. 将以下代码复制,保存为 “GenerateExportScript.sql”,或者其他名字都可以。
  2. 打开 shell
  3. sqlplus / as sysdba
  4. 使用@/path/to/GenerateExportScript.sql调用脚本,比如 SQL> @/home/oracle/GenerateExportScript.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- -----------------------------------------------------------------------------------
-- File Name : GenerateExportScript.sql
-- Author : Nimish Garg
-- Description : Export all code in the schema and generate individual files
-- Call Syntax : @GenerateExportScript
-- Last Modified: 15/09/2017
-- -----------------------------------------------------------------------------------

set serveroutput on
set termout off
set heading off
set feedback off
set linesize 50
spool ExportCode.sql

declare
l_ext varchar2(100);
begin
for c in (select distinct name, type from user_source order by 1)
loop

if c.type = 'PROCEDURE' then l_ext := '.prc';
elsif c.type = 'PACKAGE' then l_ext := '.pks';
elsif c.type = 'PACKAGE BODY' then l_ext := '.pkb';
elsif c.type = 'TRIGGER' then l_ext := '.trg';
elsif c.type = 'FUNCTION' then l_ext := '.fnc';
else l_ext := '.sql';
end if;

dbms_output.put_line('set feedback off');
dbms_output.put_line('set heading off');
dbms_output.put_line('set termout off');
dbms_output.put_line('set linesize 1000');
dbms_output.put_line('set trimspool on');
dbms_output.put_line('set verify off');
dbms_output.put_line('spool ' || c.name || l_ext);

dbms_output.put_line('prompt set define off ');
dbms_output.put_line('select decode(line,1,''create or replace '', '''' ) || text text from user_source where name = ''' || c.name || ''' and type = ''' || c.type || ''' order by type, line;' );
dbms_output.put_line('prompt /');
dbms_output.put_line('prompt set define on');

dbms_output.put_line('spool off');
dbms_output.put_line('set feedback on ');
dbms_output.put_line('set heading on ');
dbms_output.put_line('set termout on ');
dbms_output.put_line('set linesize 100 ');
dbms_output.put_line(chr(13) || chr(10));
dbms_output.put_line(chr(13) || chr(10));
end loop;
end;
/

spool off

@@ExportCode.sql