※上記の広告は60日以上更新のない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