Script to Export Pl/SQL Objects

If I have to move PL/SQL code from one schema to another, I prefer to use scripts. I have scripts to retrieve a package, procedure, or function. Another script extracts views. Yet another does triggers. Moving these types of objects is not something EXP/IMP is adept at.Ask EXP/IMP for a table and it is great. Ask EXP/IMP to give your view definition back and it is not so good.

getcode.sql

REM getcode.sql - extract any procedure, function or package
set feedback off
set heading off
set timing off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &2..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
        'PACKAGE BODY-1', '/'||chr(10),
        null) ||
        decode(line,1,'create or replace ', '' ) ||
        text text
    from all_source
    where name = upper('&&2')
    and   owner = upper('&&1')
    order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
set timing on



getallcode.sql

REM getallcode.sql - extract all procedure, function or package
set termout off
set heading off
set feedback off
set timing off
set linesize 32000
spool xtmpx.sql
select '@getcode.sql ' || upper('&&1') ||'  '|| name
from all_source
where type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
and owner = upper('&&1')
/
spool off
spool getallcode_INSTALL.sql
select '@' || name
from all_source
where type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
and owner = upper('&&1')
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
set timing on
@xtmpx.sql 


getaview.sql


REM getaview.sql
set heading off
set long 99999999
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set timing off
set embedded on
column column_name format a1000
column text format a1000
spool &2..sql
prompt create or replace view &2 (
select decode(column_id,1,'',',') || column_name column_name
from all_tab_columns
where table_name = upper('&2')
and  owner = upper('&1')
order by column_id
/
prompt ) as
select text
from all_views
where view_name = upper('&2')
and  owner = upper('&1')
/
prompt /
spool off
set termout on
set heading on
set feedback on
set verify on
set timing on


getallview.sql


REM getallview.sql
set heading off
set feedback off
set timing off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on
spool tmp.sql
select '@getaview.sql ' || '&1' ||'  '|| view_name
from all_views
where owner = upper('&1')
/
spool off
set termout on
set heading on
set feedback on
set verify on
set timing on
@tmp


getatrigger.sql


REM getatrigger.sql
set echo off
set verify off
set timing off
set feedback off
set termout off
set heading off
set pagesize 0
set long 99999999
spool &2..sql
select
'create or replace trigger "' ||
trigger_name || '"' || chr(10)||
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
chr(10) ||
triggering_event || chr(10) ||
'ON "' || table_owner || '"."' ||
table_name || '"' || chr(10) ||
decode( instr( trigger_type, 'EACH ROW' ), 0, null,
'FOR EACH ROW' ) || chr(10) ,
trigger_body
from all_triggers
where trigger_name = upper('&2')
and   owner = upper('&1')
/
prompt /
spool off
set verify on
set feedback on
set termout on
set heading on
set timing on


getallsequence.sql
REM getallsequence.sql - extract all procedure, function or package
set termout off
set heading off
set feedback off
set timing off
set linesize 32000
spool d:\allsequence.sql
SELECT 'CREATE SEQUENCE '||SEQUENCE_NAME||' START WITH '||LAST_NUMBER||' INCREMENT BY '||INCREMENT_BY||' CACHE 20 NOCYCLE NOMAXVALUE ; '
FROM all_sequences
where sequence_owner = upper('&&1')
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
set timing on