PS/SQL dereference from nested table

Question

I got a nested table with reference to other object:

tables:

create type towar as object (
  towar_id integer,
  nazwa varchar2(64),
  cena decimal(6,2)
);

create table towar_tab of towar;

create or replace type towar_zamowienie as object (
  ilosc integer,
  produkt ref towar
);
create type towar_zamowienie_tab as table of towar_zamowienie;

create table zamowienie_tab (
  id_zamowienie integer primary key,
  klient ref klient,
  towary towar_zamowienie_tab
)
nested table towary store as zamowienie_towar_nested;

procedure:

declare
  cursor zamowienia is
    select deref(klient) k, id_zamowienie id_zam, towary from zamowienie_tab;
  i integer;
  tow towar;
begin
  for r in zamowienia loop
    dbms_output.put_line(r.id_zam);
    for i in 1 .. r.towary.count loop
      --select (deref(v)) into tow from r.towary(i) v;
      dbms_output.put_line('   '||i||'. ['||r.towary(i).ilosc||'] ' || tow.nazwa);
    end loop;
  end loop;
  NULL;
end;

Now in r.towary I have nested table with produkt ref towar and I want to print field nazwa. This commented line doesn't work, it says 'SQL command not properly ended' and I have to dereference it. Normally I would just make it with relation, but I have to use objects and references.

Similar question: http://stackoverflow.com/questions/5860984/oracle-pl-sql-how-to-deref-from-a-varray-of-refs — Luke Woodward, Jun 13 '15 at 21:50

score 0 · Accepted Answer · answered Jun 13 '15 at 21:50

0

Replace the line

select (deref(v)) into tow from r.towary(i) v;

with

select deref(r.towary(i).produkt) into tow from dual;

r.towary(i) isn't a table so you can't select from it. Furthermore, tow has type towar, and r.towary(i) is a towar_zamowienie, so I assume you want to access the produkt attribute.

answered Jun 13 '15 at 21:50

Luke Woodward

63,336
16
89
104

Thanks, I forgot that `dual` contains 'local variables' – Razi91 Jun 14 '15 at 22:16

PS/SQL dereference from nested table

1 Answers1