I want to show the content of a nested table using a cursor. I've try the following but it does not work. The arguments in the "put_line" are not correct but I don't know why.
create or replace type toys_t as table of varchar2(40);
create or replace type kid_t as object (name varchar2(10), toys toys_t);
create table kid of kid_t nested table toys store as table_toys;
insert into kid values('Bob', toys_t('truck','ball','doll'));
select t.* from kid k, table(k.toys) t where k.name = 'Bob';
declare
cursor cursor_table is
select t.* from kid k, table(k.toys) t where k.name = 'Bob';
begin
for i in cursor_table loop
dbms_output.put_line(i);
end loop;
end;
It's very simple. A kid have a name and a list of toys and I want to show the toys but not from a "select" but a cursor.
Thanks from your help.
Bob
You need to reference the column name in the loop
SQL> ed
Wrote file afiedt.buf
1 declare
2 cursor cursor_table is
3 select t.* from kid k, table(k.toys) t where k.name = 'Bob';
4 begin
5 for i in cursor_table loop
6 dbms_output.put_line(i.column_value);
7 end loop;
8* end;
SQL> /
truck
ball
doll
PL/SQL procedure successfully completed.