Describe collection types
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; |
Subscribe via RSS
Shouldn’t
“CREATE OR REPLACE schema_collection_types AS”
be
“CREATE OR REPLACE VIEW schema_collection_types AS”?
Eddie Awad
1 Aug 08 at 6:42 am
Yes, looks like it got dropped when I copied it into the blog. I’ve added it back. Thanks.
maclochlainn
1 Aug 08 at 9:47 pm
Nice article.
Nag
4 Jan 13 at 1:04 pm