Retrieve collection item type information

Question

I have two types of users:

create type TEST_TYPE_WITH_CHAR as table of varchar2(100 char); create type TEST_TYPE_WITH_BYTE as table of varchar2(100 byte); 

How to determine which type contains char and which byte?

The view SYS.USER_COLL_TYPES does not provide such information. sqlfiddle .

Answer 1

Take a look at the all_coll_types . Char_used , which indicates whether the length of the varchar2 element in bytes or characters exists:

 SQL> create type t_coll_type is table of varchar2(10 byte); 2 / Type created SQL> SQL> select type_name 2 , elem_type_name 3 , char_used 4 from all_coll_types 5 where type_name = 'T_COLL_TYPE' 6 ; TYPE_NAME ELEM_TYPE_NAME CHAR_USED ------------------------------ ------------------------------ --------- T_COLL_TYPE VARCHAR2 B SQL> create type t_coll_type2 is table of varchar2(10 char); 2 / Type created SQL> SQL> select type_name 2 , elem_type_name 3 , char_used 4 from all_coll_types 5 where type_name = 'T_COLL_TYPE2' 6 ; TYPE_NAME ELEM_TYPE_NAME CHAR_USED ------------------------------ ------------------------------ --------- T_COLL_TYPE2 VARCHAR2 C