参考：
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