Maclochlainn's Weblog

The view is fairly straightforward and written to let you deploy it in any schema. You’ll need to make changes if you’d like it work against the ALL or DBA views.

COL type_name FORMAT a30
COL elem_type_name FORMAT a38


CREATE OR REPLACE VIEW schema_collection_types AS
SELECT   ct.type_name
,        DECODE(ct.coll_type,'TABLE','TABLE OF '
,'VARYING ARRAY','VARRAY('||ct.upper_bound||') OF ')
||       DECODE(ct.elem_type_name
,          'BFILE'        ,'BINARY FILE LOB'
,          'BINARY_FLOAT' ,ct.elem_type_name
,          'BINARY_DOUBLE',ct.elem_type_name
,          'BLOB'         ,ct.elem_type_name
,          'CLOB'         ,ct.elem_type_name
,          'CHAR'         ,DECODE(NVL(ct.length,0),0,ct.elem_type_name
, ct.elem_type_name||'('||ct.length||')')
,          'DATE'         ,ct.elem_type_name
,          'FLOAT'        ,ct.elem_type_name
,          'LONG RAW'     ,ct.elem_type_name
,          'NCHAR'        ,DECODE(NVL(ct.length,0),0,ct.elem_type_name
, ct.elem_type_name||'('||ct.length||')')
,          'NVARCHAR2'    ,DECODE(NVL(ct.length,0),0,ct.elem_type_name
, ct.elem_type_name||'('||ct.length||')')
,          'NUMBER'       ,DECODE(NVL(ct.precision||ct.scale,0)
, 0,ct.elem_type_name
, DECODE(NVL(ct.scale,0),0
, ct.elem_type_name||'('||ct.precision||')'
, ct.elem_type_name||'('||ct.precision||','
|| ct.scale||')'))
,          'RAW'          ,DECODE(NVL(ct.length,0),0,ct.elem_type_name
, ct.elem_type_name||'('||ct.length||')')
,          'VARCHAR'      ,DECODE(NVL(ct.length,0),0,ct.elem_type_name
, ct.elem_type_name||'('||ct.length||')')
,          'VARCHAR2'     ,DECODE(NVL(ct.length,0),0,ct.elem_type_name
, ct.elem_type_name||'('||ct.length||')')
,          'TIMESTAMP'     , ct.elem_type_name,ct.elem_type_name) AS elem_type_name
FROM     user_coll_types ct
WHERE    ct.coll_type IN ('TABLE','VARYING ARRAY')
ORDER BY ct.coll_type;