-- trig2file.sql
-- ---------------
--
--  by Doug Kiz
--
--  meta SQL script.
--
--   This script will generate an SQL scriptt to read all trigger source 
--     into text flat files.
--
--   You will be prompted for a save directoory -- which must exist.  Enter
--     the path to the directory where you wwant the files
--     created.  End the path with a slash oor backslash.
--
--  UNIX example:
--    Enter the save directory (e.g. c:\spoool\): /home/mydir/triggers/
--
-- How to use this script:
--
-- 1) If necessary, create the SAVE_PATH dirrectory
-- 2) Connect to the database as a DBA user
-- 3) Run this script (i.e. @trig2file)
-- 4) Enter the user to be archived
-- 5) Run the generated script (i.e. @run)
-- 6) If desired, drop the generated script  (i.e. !rm run.sql)
--
-- Remember: you run this script first, thenn run the script it creates.
--
-- Troubleshooting:
--
--  You may find that your larger files get  truncated.  If this occurs,
--   try the following:
--
--    * If the description is cut off, increease the value of
--         prompt set linesize
--
--    * If the trigger body not all there, iincrease the value of
--         prompt long
--
-- --------------------------------
accept SAVE_PATH char prompt "  Enter the save directory (e.g. c:\spool\): "

set trimspool on
set pagesize 0
set linesize 400
set feedback off
set tab off

column dummy1 noprint
column dummy2 noprint
column command word_wrapped

define spool_file = &SAVE_PATH.run.sql
spool &spool_file.

prompt set trimspool on
prompt set pagesize 0
prompt set linesize 400
prompt set feedback off
prompt set tab off
prompt set arraysize 1
prompt set long 80000
prompt set longchunksize 400

select 'spool &SAVE_PATH' || lower(owner) || '.' || lower(trigger_name)|| '.sql' command
     , 0 dummy1
     , trigger_name dummy2
  from dba_triggers
 where owner not in ('SYS', 'SYSTEM')
union
select 'select ''create or replace trigger ''|| lower(owner) || ''.'' || description, trigger_body '||
         'from dba_triggers where trigger_name = ''' || trigger_name ||
         ''' and owner = '''|| owner||
         ''';' command,
       2 dummy1, trigger_name dummy2
  from dba_triggers
 where owner not in ('SYS', 'SYSTEM')
union
select 'prompt /' command,
       3 dummy1, trigger_name dummy2
  from dba_triggers
 where owner not in ('SYS', 'SYSTEM')
union
select 'spool off' command,
       4 dummy1, trigger_name dummy2
  from dba_triggers
 where owner not in ('SYS', 'SYSTEM')
 order by dummy2, dummy1;

spool off

prompt "  Now run @&spool_file. to generate the individual source files"