Saturday, May 26, 2012

Script to Report Collection Types

This script provides documentation with respect to the different collection types in the database.
REM LOCATION:   Object Management\Collections
REM FUNCTION:   Document the collection types in the database
REM TESTED ON:  8.0.4.1, 8.1.5, 8.1.7, 9.0.1, 10.2.0.3, 11.1.0.6
REM PLATFORM:   non-specific
REM REQUIRES:   dba_coll_types
REM
REM
REM ******************** Oracle Administration ********************


COLUMN owner           FORMAT a10 HEADING 'Collec.|Owner'
COLUMN type_name       FORMAT a16 HEADING 'Type|Name'
COLUMN coll_type       FORMAT a15 HEADING 'Collec.|Type'
COLUMN upper_bound                HEADING 'VARRAY|Limit'
COLUMN elem_type_owner FORMAT a10 HEADING 'Elementary|Type|Owner'
COLUMN elem_type_name  FORMAT a11 HEADING 'Elementary|Type|Name'
SET PAGES 58 LINES 78 VERIFY OFF FEEDBACK OFF
TTITLE 'Collection Type Report'
SELECT owner, type_name, coll_type, upper_bound, elem_type_owner,
       elem_type_name
  FROM dba_coll_types
 WHERE owner LIKE '%' || UPPER ('&owner') || '%';