MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Describe collection types

with 3 comments

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.

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;

You can query the view by formatting the columns, like this:

COL type_name      FORMAT a30
COL elem_type_name FORMAT a38
SELECT * FROM schema_collection_types;

Written by maclochlainn

July 31st, 2008 at 4:38 am

Posted in Uncategorized

3 Responses to 'Describe collection types'

Subscribe to comments with RSS or TrackBack to 'Describe collection types'.

  1. Shouldn’t

    “CREATE OR REPLACE schema_collection_types AS”


    “CREATE OR REPLACE VIEW schema_collection_types AS”?

    Eddie Awad

    1 Aug 08 at 6:42 am

  2. Yes, looks like it got dropped when I copied it into the blog. I’ve added it back. Thanks.


    1 Aug 08 at 9:47 pm

  3. Nice article.


    4 Jan 13 at 1:04 pm

Leave a Reply