declare type two_cols record ( family_id family_members.family_id %type, city family_members.city%type ); type family_members_t table of two_cols; l_family_members family_members_t; begin select family_id,city bulk collect l_family_members (select x.family_id, x.city, x.member_count,row_number() on (partition x.family_id order x.member_count desc) rn (select family_id, city, count(*) member_count family_members group family_id, city) x) y y.rn = 1; rec in 1..l_family_members.count loop dbms_output.put_line('majority mem of family id' || l_family_members.family_id(rec) || 'stay in '||l_family_members.city(rec)); end loop; end; error:
ora-06550: line 23, column 69: pls-00302: component 'family_id' must declared ora-06550: line 23, column 1: pl/sql: statement ignored 06550. 00000 - "line %s, column %s:\n%s" *cause: pl/sql compilation error. *action:
i confused @ output line.. not getting how retrieve data bulk collect there 2 columns in it..how distinguish them , retrieve them?
you trying select 2 columns 1 record doesn't work. depending on database version, may able select records bulk collected table follows
declare type two_cols record ( family_id family_members.family_id %type, city family_members.city%type ); type family_members_t table of two_cols; l_family_members family_members_t; begin select two_cols(family_id,city ) bulk collect l_family_members (select x.family_id, x.city, x.member_count,row_number() on (partition x.family_id order x.member_count desc) rn (select family_id, city, count(*) member_count family_members group family_id, city) x) y y.rn = 1; rec in 1..l_family_members.count loop dbms_output.put_line('majority mem of family id' || l_family_members(rec).family_id || 'stay in '||l_family_members(rec).city); end loop; end; nb: fixed reference in output loop put (rec) after table , before column
Comments
Post a Comment