Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create TYPE a_r IS object
(
a1      varchar(1),
        a2      varchar(1)
    );

  
create TYPE a_t IS TABLE OF a_r;

    
CREATE or replace FUNCTION get_a
    RETURN a_t
    PIPELINED
IS
  l_a_t a_t := new a_t();
BEGIN
    FOR c IN (SELECT '1' a , '2' b FROM DUAL)
    LOOP
        l_a_t.extend;
        l_a_t( l_a_t.count ) := a_r(c.a,c.b);
    END LOOP;
    FOR a
            IN (SELECT 'a2' a,
                       'b' b
                  FROM DUAL)
        LOOP
            l_a_t.extend;
            l_a_t( l_a_t.count ) := a_r(a.a,a.b);
        END LOOP;
        
   for i in 1 .. l_a_t.count
   loop
     pipe row( l_a_t(i) );
   end loop;
exception
        WHEN VALUE_ERROR
        THEN
            DBMS_OUTPUT.put_line ('VALUE_ERROR');
--        WHEN OTHERS
--        THEN
--            DBMS_OUTPUT.put_line ('other');
    
END;
/
select * from table( get_a );