PL/SQLソース出力 - ぐま@Wiki

【Oracle】-SQL-PL/SQLソース出力

set define on
set verify off
accept pg_name prompt 'プログラム名を入力してください：'
col owner format a20
select
    owner
from
    dba_source
where
    name = upper('&&pg_name')
group by
    owner
;
accept owner_name prompt 'オーナー名を入力してください：'
set pagesize 0
set feedback off
set trimspool on
set heading off
col text format a4000
set line 4000
set escape on
spool &&pg_name\.sql
select
    case when upper(text) like 'PACKAG%' || upper('&&pg_name') || '%' then 'CREATE OR REPLACE ' || text
    when upper(text) like 'END%' || upper('&&pg_name') || '%' then text || '/'
    else text
    end as text
from
    dba_source
where
    name = upper('&&pg_name')
    and owner = upper('&&owner_name')
order by
    owner,
    name,
    type,
    line
;
spool off

set pagesize 24
set feedback on
set heading on
set line 100
prompt 'create file name - ' || &&pg_name\.sql
set escape off