Tiny Meta-Script for Showing Stored Procedure Code

From: Thomas B Cox <Thomas.B.Cox_at_tek.com>
Date: Mon, 19 Dec 1994 23:03:46 GMT
Message-ID: <Thomas.B.Cox.3.2EF61152_at_tek.com>

I haven't seen anything like this float by recently, so here it is, for what it's worth.

The following SQL*Plus script will create another script. That second script will, for each stored procedure owned by the target user, pull up the source code and print it below a simplistic header.

Attached below that is the output of the meta-script. And finally, below that, is the output of the second script.

Modifications and enhancements are welcome. Uses 8-character file names.

 -Tom Cox

REM
REM MkCodeLs.SQL
REM
REM created 19-DEC-94 by Thomas Cox
REM
REM meta-script that creates a script that iteratively selects REM code from the data dictionary table ALL_SOURCE REM owned by a given user.
REM set heading off
set pagesize 0
set feedback off
spool CodeList.sql
prompt REM this is the file CodeList.SQL

select distinct  'prompt                      ' 

, name
, type
, 'select text from all_source where owner = ''WH000'' '
, ' and name = '
, '''' || name || ''''
, ' and type = '
, '''' || type || ''''
, ' order by line ;'

from all_source
where owner = 'WH000'
/
spool off
set heading on
set feedback on
prompt File 'Codelist.SQL' created

---

REM this is the file CodeList.SQL

prompt                       PKG_INVTRY_ALLOC               PACKAGE            
select text from all_source where owner = 'WH000'   and name =                 
'PKG_INVTRY_ALLOC'                and type =  'PACKAGE'       order by line ;  
                                                                               
================================

SQL> _at_codelist

PKG_INVTRY_ALLOC               PACKAGE
PACKAGE  pkg_invtry_alloc IS                                                   
PROCEDURE alloc_inventory ( line_item_id number ) ;                            
END;                                                                           

3 rows selected.

SQL> spool off Received on Tue Dec 20 1994 - 00:03:46 CET