The object definations and the function are below.Instead of varray I have taken a table for ppp. It could be either ways.The ref cursor defination is not mandatory, still I have used a type for the same
The commented line in the function is what is not working. I have also tried printing the values of that I am assigning. It is getting printed.
For tables RMP_USER and RMP_TEAM, you could use any table with some relationship or you could choose to only work with one table

CREATE OR REPLACE
TYPE ppp IS TABLE OF VARCHAR2(20);
/
CREATE OR REPLACE
TYPE user_obj
AS OBJECT
(
 user_id      VARCHAR2 (8),
 unit_array   ppp
);
/
CREATE OR REPLACE
type user_table is table of user_obj;
/
 
CREATE OR REPLACE PACKAGE types
AS    TYPE ref_cursor IS REF CURSOR;
END;
/

CREATE OR REPLACE FUNCTION test_refcursor3 (p_user_id VARCHAR2)
   RETURN TYPES.ref_cursor
IS
   w_p                 ppp;
   user_table_object   user_table       := user_table ();
   local_ref_cursor    TYPES.ref_cursor;

   CURSOR all_user_cur
   IS
      SELECT user_id
        FROM rmp_user;

   CURSOR unit_cursor (p_user_id VARCHAR2)
   IS
      SELECT unit_id
        FROM rmp_user a, rmp_team b
       WHERE a.user_id = p_user_id AND a.team_id = b.team_id
      UNION
      SELECT unit_id
        FROM rmp_unit_rmp_user
       WHERE user_id = p_user_id;

   i INTEGER          := 1;
BEGIN
   
   FOR all_user_cursor_var IN all_user_cur
   LOOP
      
      OPEN unit_cursor (all_user_cursor_var.user_id);

      FETCH unit_cursor
      BULK COLLECT INTO w_p;

      CLOSE unit_cursor;

      user_table_object.EXTEND;
      user_table_object (i) := user_obj (all_user_cursor_var.user_id, w_p);
      i := i + 1;

      DBMS_OUTPUT.put_line (   'test_refcursor2....user_id'
                            || user_table_object(i).user_id
                           );

      FOR j IN w_p.FIRST .. w_p.LAST
      LOOP
         DBMS_OUTPUT.put_line (   'test_refcursor2....unit_array'
                               || user_table_object(i).unit_array (j)
                              );
      END LOOP;

   END LOOP;

   open local_ref_cursor for select user_table_object from dual;
   RETURN local_ref_cursor;
END test_refcursor3;

/
 
 
Regards,
Suman John
Infosys Technologies Ltd
Bangalore, India
W: +91 80 41059080
 

--------------------------------------------------------------------------------
From: suman john 
Sent: Wednesday, July 04, 2007 4:56 PM
To: Neeraj Kumar10
Cc: suman john
Subject: query
 
CREATE OR REPLACE
TYPE ppp IS TABLE OF VARCHAR2(20);
/
 
CREATE OR REPLACE
TYPE user_obj
AS OBJECT
(
 user_id      VARCHAR2 (8),
 unit_array   ppp
);
/
 
CREATE OR REPLACE
type user_table is table of user_obj;
/
 
 
CREATE OR REPLACE PACKAGE types
AS    TYPE ref_cursor IS REF CURSOR;
END;
/
 
 
CREATE OR REPLACE FUNCTION TEST_refcursor2 (p_user_id VARCHAR2) 
RETURN types.ref_cursor IS
   w_p ppp ;
   user_table_object user_table;
   local_ref_cursor types.ref_cursor;  
   
   cursor all_user_cur is select user_id from rmp_user;
    
   cursor unit_cursor(p_user_id varchar2) IS select unit_id from rmp_user a,rmp_team b where a.user_id = p_user_id and a.team_id =b.team_id
   union  select unit_id from rmp_unit_rmp_user where user_id =p_user_id;
   i integer :=1;
BEGIN
   dbms_output.put_line('test_refcursor2....');
   for all_user_cursor_var in all_user_cur
         loop
         dbms_output.put_line('test_refcursor2....count' || i);
               open unit_cursor(all_user_cursor_var.user_id);
               fetch unit_cursor bulk collect into w_p; 
               close unit_cursor;  
              user_table_object(i).user_id :=all_user_cursor_var.user_id;
              user_table_object(i).unit_array := w_p;
              i:=i+1;
             -- dbms_output.put_line('test_refcursor2....user_id' || user_table_object(i).user_id);
              --dbms_output.put_line('test_refcursor2....unit_array' || user_table_object(i).unit_array);
              /* OPEN  local_ref_cursor FOR
                    SELECT user_id,w_p from rmp_user where user_id = p_user_id;*/
         end loop;
        -- open local_ref_cursor for select user_table_object from dual;
     RETURN local_ref_cursor;
 END TEST_refcursor2;
/