Oracle PL/SQL nested table processing comparison

Question

I am working with Oracle 11gR2 PL/SQL tables (nested table), but just getting a little confused on whether the following two approaches are achieving the same result, i.e.:

Assuming I have defined the following TYPE:

type member_nos_type is table of varchar2(32000);

and the following sample table definition/data:

table: my_members

id        number not null,
membernos varchar2(30) not null,
first_nm  varchar2(255),
surname   varchar2(255)

Sample data in table my_members:

1  111111  Joe   Smith
2  222222  Sam   Smith
3  333333  Jane  Smith
4  444444  Jason Smith
5  555555  Joel  Smith

then within a procedure, I have the following code:

    procedure a1 (p_param1 in varchar2) is

      v_member_list        member_nos_type;

    begin
      if p_param1 = 'BASKETBALL' then
        v_member_list := member_nos_type ('222222','444444');
      end if;
    end;    
----------------------------    
    procedure b2 (p_param1 in varchar2) is

      v_member_list        member_nos_type;

    begin
      if p_param1 = 'BASKETBALL' then
        v_member_list := member_nos_type (
          SELECT LISTAGG(membernos, ',') WITHIN GROUP (ORDER BY membernos)
          FROM my_members
          WHERE id in (2,4));
      end if;
    end;

Based on procedure a1 and procedure b2 above, is the end result a table of member numbers of type varchar2 ?

Update

How can I return a TRUE nested table based on my membernos column data in my my_members table?

Did you not run it? LISTAGG returns varchar2 - docs.oracle.com/cd/E11882_01/server.112/e41084/… — OldProgrammer
Procedure a1 makes true nested table type, while second procedure uses LISTAGG which returns one string, just values are separated by comma. — Oto Shavadze
Ok, as what I suspected. Could someone pls let assist on how I would go about returning a true nested table, like I was attempting to with the LISTAGG function based on my membernos column data in my my_members table? — tonyf
Please show some sample expected output, because not clear what you are attempting to do. What exactly is "nested" here? I don't see anything hierarchical. — OldProgrammer

XING XING · Accepted Answer · 2017-02-26T20:58:46

I doubt if in that way you could populate a nested table. You can do it as below;

procedure b2 (p_param1 in varchar2) is

      v_member_list        member_nos_type;

    begin
      if p_param1 = 'BASKETBALL' then
      for rec in (Select membernos
                  from my_members
                  WHERE id in (2,4))
      loop
       v_member_list.extend();     
       v_member_list(rec):= rec.membernos;    

      End loop;   
      end if;

      For i in 1..v_member_list.count
      loop

       dbms_output.put_line(v_member_list(i));

      end loop;

    end;

PS: Not tested.

Oracle PL/SQL nested table processing comparison

Update

2 Answers