Re: Listing all Stored Procedure in database

From: LKBrwn_DBA <member30625_at_dbforums.com>
Date: Fri, 11 Jul 2003 18:20:54 +0000
Message-ID: <3101131.1057947654@dbforums.com>

Try something like this:

/*

        db_extract_proc.sql
*/

def dbadir='/export/home/oracle/oradba/sql' def tmpdir='/export/home/oracle/oradba/tmp' def owner='GIPSYS'

set echo off verify off head off feed off pages 0 set term off recsep off timing off time off

col o0 new_value dir noprint
break on report
select lower('&owner') o0 from dual
/

spo &&tmpdir/db_creaproc.sql

select

  'set lin 256 trims on pages 0'||chr(10)

||'set echo off verify off head off feed off'||chr(10)
||'set term off recsep off timing off time off'||chr(10)
||'spo &&tmpdir/'||a.name||'.pls'||chr(10)
||'select text from dba_source '||chr(10)
||' where owner= '||chr(39)||'&&owner'||chr(39)||chr(10)
||' and name = '||chr(39)||a.name||chr(39)||chr(10)
||'/'||chr(10)
||'spo off'||chr(10)

from (select distinct name from dba_source

       where owner = '&owner') a
/
spo off
cl buff
@&&tmpdir/db_creaproc.sql
cl buff

--
Posted via http://dbforums.com

Received on Fri Jul 11 2003 - 13:20:54 CDT