Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Create view to describe collection datatypes

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.

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;

Written by maclochlainn

July 31, 2008 at 4:38 am

3 Responses

Subscribe to comments with RSS.

  1. […] You can find it here … Tagged with: all_coll_types, dba_coll_types, UDT collection, user_coll_types « How to copy external files with Java and PL/SQL wrappers […]

  2. Shouldn’t

    “CREATE OR REPLACE schema_collection_types AS”


    “CREATE OR REPLACE VIEW schema_collection_types AS”?

    Eddie Awad

    August 1, 2008 at 6:42 am

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


    August 1, 2008 at 9:47 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: