-- source2file.sql
-- ---------------
--
--  meta SQL script.
--
--   This script will generate an SQL script to read all source from 
--     non-SYS/SYSTEM userids into flat filles.
-- --------------------------------
set trimspool on
set pagesize 0
set linesize 400
set feedback off
set tab off

column dummy1 noprint
column dummy2 noprint
column dummy3 noprint
column command word_wrapped

accept SAVE_PATH char prompt "  Enter the save directory (e.g. c:\spool\): "
accept DIR_SEPAR char prompt " Enter the directory separator ('\' or '/'): "

-- # Create a subdirectory for each source owner
-- ##########
spool mkDir.sql
select distinct 'host mkdir &SAVE_PATH.' || lower(owner) || '&DIR_SEPAR.' command
  from all_source
 where owner not in ('SYS', 'SYSTEM')
/
spool off
@mkDir.sql


-- # Create script to convert source to flat files
-- ##########
spool run.sql

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

select 'spool &SAVE_PATH' || lower(owner) || '&DIR_SEPAR.' || lower(owner) || '.' || lower(name) ||
           decode(type, 'PACKAGE BODY','_bdy.sql','.sql') command,
       0 dummy1, name dummy2, type dummy3
  from all_source
 where owner not in ('SYS', 'SYSTEM')
union
select 'select decode(line,1,''create or replace ''||text,text) '||
         'from all_source' || chr(10) || ' where owner = ''' || owner || ''' and name = ''' || name || ''' '||
         'and type = '''|| type|| ''';' command,
       2 dummy1, name dummy2, type dummy3
  from all_source
 where owner not in ('SYS', 'SYSTEM')
union
select 'prompt /' command,
       3 dummy1, name dummy2, type dummy3
  from all_source
 where owner not in ('SYS', 'SYSTEM')
union
select 'spool off' command,
       4 dummy1, name dummy2, type dummy3
  from all_source
 where owner not in ('SYS', 'SYSTEM')
 order by dummy2, dummy3, dummy1;

spool off