refcursor and nested table

[KoZo]

I have a stored procedure which take back a refcursor containing nested table.
When I try to access to the nested table, I get a No such field exception. When I used a select tratement, returning identical cursor, it worked fine. When I call the procedure from PL/SQL developer, the refcursor contains forrect values and nested tables.

oqc := TOracleQuery.Create(nil);
try
  oqc.session := os1;
  os1.Connected := true;
  oqW.SetVariable('pCount', 7);
  oqW.SetComplexVariable('pLista', oqc);
  oqW.Execute;
  try
    while not oqc.Eof do begin
      fo := oqc.ObjField('NT');
      if fo.IsCollection then begin
        for i := 0 to fo.ElementCount - 1 do begin
          if fo.ElementExists(i) then begin
            iv := fo[i].GetAttr('NID');
           end;
        end;
      end;
    end;
    oq1.next;
  end;
finally
  oqc.Close;
end;

 

[Marco Kalter]

Can you let me know which statement causes exactly what exception?

 

[KoZo]

Hello Marco,

The statement:

fo := oqc.ObjField('NT');

Exception: Field NT does not exists

oqW.SQL:

begin
  pack_kozo.collectionTest(pCount => :pCount, pLista => :pLista);
end;

pack_kozo.collectionTest:

procedure collectionTest(pCount in integer, pLista in out sys_refcursor) is
  begin
    open pLista for
      select * from table(pack_070622.Get_Lista(pCount));
  end collectionTest;

When I used the collowing code, the code worked fine

oqc := TOracleQuery.Create(nil);
  try
    oqc.session := os1;
    os1.Connected := true;
    oqc.SQL.Text := 'select * from table(pack_070622.Get_Lista(7))';
    oqc.Execute;
    try
      while not oqc.Eof do begin
        fo := oqc.ObjField('NT');
        if fo.IsCollection then begin
          for i := 0 to fo.ElementCount - 1 do begin
            if fo.ElementExists(i) then begin
              iv := fo[i].GetAttr('NID');
            end;
          end;
        end;
      end;
      oqc.next;
    end;
  finally
    oqc.Close;
  end;

Thank you for help.

Best regards,
KoZo