Oracle COLLECT function creates a new collection data type with a random name

Question

The Oracle'c COLLECT function starts creating a new collection type. Is there any way to disable this behavior?

that's what happens ...

check existing user types

select object_name from user_objects where object_type = 'TYPE'
no rows selected.

create a user data type using a collection of type VARRAY

CREATE OR REPLACE TYPE TEST_T  
   AS OBJECT (C1 VARCHAR2(20 BYTE), C2 VARCHAR2 (11 Byte));
CREATE OR REPLACE TYPE ARRAY_TEST_T AS VARRAY(200) OF TEST_T;

check types ...

select object_name from user_objects where object_type = 'TYPE'

OBJECT_NAME                                                                     
------------
TEST_T                                                                          
ARRAY_TEST_T 

2 rows selected.

Now this request will cause the creation of a new collection type:

select cast(collect(TEST_T(c1,c2)) AS ARRAY_TEST_T) 
from (  select '1.1' as c1, '1.2' as c2 from dual ) ;

check the types again ...

select object_name from user_objects where object_type = 'TYPE'
OBJECT_NAME                                                                     
-----------------------------
TEST_T                                                                          
SYSTP5Iel7MEkRT2osGnB/YcB4A==                                                   
ARRAY_TEST_T                                                                    

3 rows selected.

Oracle created a new collection type "SYSTP5Iel7MEkRT2osGnB / YcB4A ==" with the following specification:

CREATE OR REPLACE TYPE "SYSTPzGCo9gclT3WmlUX5SNtEPg==" AS TABLE OF TEST_T

Answer 1

Read http://www.oracle-developer.net/display.php?id=306

I think it will work when you define:

ARRAY_TEST_T AS TEST_T;

, (200)... ....