select * from table (tmp ('123456789') )  ;

CREATE OR REPLACE TYPE type_gsn IS TABLE OF obj_gsn;
/

CREATE OR REPLACE TYPE obj_gsn as   object (
--SOURCE_TABLE	VARCHAR2 (250 ),
a1		VARCHAR2 (250 ),
a2		DATE,
a3		VARCHAR2 (250 ),
a4		VARCHAR2 (250 ),
a5		NUMBER,
a6		NUMBER,
a7		NUMBER
);
/


CREATE OR REPLACE function tmp (msisdn in varchar2)  return type_gsn as 

v_type_gsn type_gsn;
v_type_gsn_tmp type_gsn;


type rec_xml is record (table_owner varchar2(31),
                        table_name varchar2(31), 
                        partition_name varchar2(31),
                        date# date,
                        high_value varchar2(1000)
                        );
                        
type  obj_xml is table of rec_xml;                   
v_obj_xml obj_xml;

begin 


WITH xml AS 
(SELECT xmltype(DBMS_XMLGEN.getxml('SELECT table_owner,table_name, partition_name, high_value 
                                      FROM dba_tab_partitions
                                     WHERE table_owner = ''TEST''  and table_name=''TEST''
                                       ')) AS xml
   FROM DUAL),
    parsed_xml AS
(SELECT EXTRACTVALUE (xs.object_value, '/ROW/TABLE_OWNER')    AS table_owner,
        EXTRACTVALUE (xs.object_value, '/ROW/TABLE_NAME')     AS table_name,
        EXTRACTVALUE (xs.object_value, '/ROW/PARTITION_NAME') AS partition_name,
        EXTRACTVALUE (xs.object_value, '/ROW/HIGH_VALUE')    AS high_value
   FROM xml x, table (XMLSEQUENCE (EXTRACT (x.xml, '/ROWSET/ROW'))) xs)
SELECT table_owner,table_name, partition_name,to_date( substr(high_value,11,19)  , 'yyyy-mm-dd hh24:mi:ss') date#, high_value  bulk collect into v_obj_xml  FROM parsed_xml
where to_date( substr(high_value,11,19)  , 'yyyy-mm-dd hh24:mi:ss')  between sysdate-5 and sysdate
order by high_value;


 for i in  v_obj_xml.first..v_obj_xml.last
  
loop




execute immediate 'select /*+ parallel(4) */ obj_gsn(a1,a2,a3,a4,a5,a6,a7) from '||
v_obj_xml(i).table_owner ||'.'||v_obj_xml(i).table_name||' partition('||v_obj_xml(i).partition_name||') where msisdn=:1' 
                    bulk collect  into v_type_gsn  using   msisdn;



v_type_gsn_tmp:=v_type_gsn_tmp multiset union all v_type_gsn;

  

 end loop;                  
                                                                          


return v_type_gsn_tmp; 

end;
/

v_type_gsn_tmp:=v_type_gsn_tmp multiset union all v_type_gsn;

drop type rec force;
create or replace type rec as  object  (id number, name varchar2(10));

drop type obj force;
create or replace type obj is table of rec;

create or replace function test_collection_r1 return obj  as 
v_obj obj;
v_obj_tmp  obj;

begin

for c in 1..5 
loop
--v_obj_tmp:=v_obj;

execute immediate 'select  rec(to_number(1), to_char(''a'') )from dual ' bulk collect into v_obj;

v_obj_tmp:=v_obj_tmp multiset union all  v_obj;

end loop;

return v_obj;

end;

SQL> select  * from table( test_collection_r1() )

        ID NAME      
---------- ----------
         1 a         
1 row selected.

SQL> create or replace function test_collection_r1 return obj  as 
v_obj obj;
v_obj_tmp  obj;

begin

for c in 1..5 
loop
v_obj_tmp:=v_obj;

execute immediate 'select  rec(to_number(1), to_char(''a'') )from dual ' bulk collect into v_obj;

v_obj_tmp:=v_obj_tmp multiset union all  v_obj;

end loop;

return v_obj_tmp;

end;
Function created.
SQL> select  * from table( test_collection_r1() )

        ID NAME      
---------- ----------
         1 a         
         1 a         

2 rows selected.