This report lists all stored code in the database. It can report on all objects, all objects by owner, or on a specific object name.
REM LOCATION: Object Management\Functions,Procedures, and Packages
REM FUNCTION: Report on Stored Code
REM TESTED ON: 10.2.0.3, 11.1.0.6 (not tested but should work on previous versions)
REM PLATFORM: non-specific
REM REQUIRES: dba_objects, proc_count
REM NOTES: Must be run from an account with SYSDBA privileges.
REM
REM
REM ******************** Oracle Administration ********************
REM
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_OBJECT_NAME
SET pages 53 lines 80 verify off feedback off echo off
COLUMN owner format a10
COLUMN name format a30
COLUMN type format a9 word_wrapped
COLUMN status format a7
COLUMN lines format 9,999
BREAK on owner skip page on type
TTITLE "Report on Stored Code"
WITH proc_count AS
(SELECT owner, NAME, TYPE, COUNT (*) lines
FROM dba_source
WHERE owner LIKE UPPER ('&&ENTER_OWNER_NAME')
AND NAME LIKE UPPER ('&&ENTER_OBJECT_NAME')
GROUP BY owner, NAME, TYPE)
SELECT b.owner owner, b.object_type TYPE, b.object_name NAME,
b.status status, b.last_ddl_time modified, lines
FROM dba_objects b, proc_count a
WHERE b.owner NOT IN ('SYS', 'SYSTEM')
AND b.owner = a.owner
AND b.object_type = a.TYPE
AND b.object_name = a.NAME
AND b.owner LIKE UPPER ('&&ENTER_OWNER_NAME')
AND b.object_name LIKE UPPER ('&&ENTER_OBJECT_NAME')
ORDER BY 1, 2, 3;
