Pre-defined collection types in Oracle

In his comment, Gary alerts me of the fact that Oracle has a pre-defined collection type called dbms_debug_vc2coll. It is owned by sys and granted to public. I have also discovered that there is another pre-defined collection type called ku$_vcnt and it is also owned by sys and granted to public. However, ku$_vcnt is a table of varchar2(4000), whereas dbms_debug_vc2coll is a table of varchar2(1000):

sys@EDDEV> select owner, type_name, coll_type,
  2    elem_type_name, length
  3  from dba_coll_types
  4  where type_name in
  5   ('DBMS_DEBUG_VC2COLL', 'KU$_VCNT');

OWN TYPE_NAME            COLL_ ELEM_TYP     LENGTH
--- -------------------- ----- -------- ----------
SYS DBMS_DEBUG_VC2COLL   TABLE VARCHAR2       1000
SYS KU$_VCNT             TABLE VARCHAR2       4000

Here is an example of how you can use these pre-defined collection types:

scott@EDDEV> column column_value format a20
scott@EDDEV> select * from
  2  table(sys.dbms_debug_vc2coll(1,2,'a','b'));

COLUMN_VALUE
--------------------
1
2
a
b

scott@EDDEV> select * from
  2  table(sys.ku$_vcnt(1,2,'a','b'));

COLUMN_VALUE
--------------------
1
2
a
b

Whether it is advisable to use these pre-defined types in your production applications, or just create and use your own types is another (debatable) issue. However, If there is the slightest chance that these pre-defined variables are going to disappear in future Oracle releases, it becomes obvious who wins the debate.

Possibly related:

Tagged collection-type | Post a Comment

Comments (2)

William Robertson wrote:

Handy query for reminding yourself what collection types are available (predefined or otherwise):

SELECT ct.owner, ct.type_name, ct.elem_type_name, ct.length
FROM   all_coll_types ct
     , all_types ot
WHERE  ct.coll_type = 'TABLE'
AND    ot.type_name(+) = ct.elem_type_name
AND    ot.owner(+) = ct.elem_type_owner
AND    ot.type_name IS NULL
ORDER BY ct.owner, ct.type_name;

January 24th, 2006, at 10:13 am #