Get Multiset field value of Oracle Query on Delphi with ODAC

Question

Hellо! I want to get Multiset field value of Oracle Query on Delphi 7.

For example on Delphi:

QSQl.SQL.Text := "select cast (multiset (select id, summa from temp) as nach_table) t from dual";

I wrote this code:

temp: TOraNestTable;
...
temp :=  QSQl.fieldByName('t').AsTable;

But there are no AsTable property

How can I retrive multiset query field in table?

GolezTrol · Answer 1 · 2020-03-02T11:58:18.207

I bumped into this question looking for the same answer. I realize the question is a bit old and OP has been inactive for well over 2 years, but there is very little information on this, so I thought I'd answer anyway. This is what I found out. Since there are so few examples, I don't know if this is the way, but with just 2 lines of code and what appears to be great performance, I don't think it's bad. :o)

The answer

TOraQuery has a GetTable method to which you can pass a fieldname (t in your case). This returns a TNestTable object, which you can assign to the table property of the TOraNestedTable component. Note that I use the AfterScroll event, so it is assigned whenever you change records in your main level.

Since you were selecting from dual, that wouldn't really be necessary, but I guess that was just for the example.

procedure TForm1.OraQuery1AfterScroll(DataSet: TDataSet);
begin
  OraNestedTable1.Table := OraQuery1.GetTable('t');
  OraNestedTable1.Active := True;
end;

A level deeper

In addition, if you'd need to add a second level, it works slightly different. TOraNestedTable doesn't have a GetTable method, but you can use the GetObject method. The returned object can be of various types, but if you got a multiset inside a multiset, it will be of type TOraNestTable again. I used this to get the orders and their orderlines for a customer.

So my query looked like this:

select
  c.CUSTOMERID,
  c.FIRSTNAME,
  -- All orders of the customer
  cast(multiset(
    select 
      ORDERID,
      ORDERDATETIME,
      -- All orderlines of the order
      cast(multiset(
        select 
          ORDERLINEID,
          PRODUCTID, 
          QUANTITY
        from
          VAN_ORDERLINE ol
        where 
          ol.ORDERID = o.ORDERID
      ) as ORDERLINE_T) as ORDERLINES

    from
      VAN_ORDER o
    where 
      o.CUSTOMERID = c.CUSTOMERID
  ) as ORDER_T) as ORDERS
from
  CUSTOMER c
where
  c.CUSTOMERID in (1, 2, 3)

Then the code above was used, with the fieldname ORDERS, to get all the orders of the customer as a detail using TOraQuery.GetTable.

procedure TForm1.OraQuery1AfterScroll(DataSet: TDataSet);
begin
  OraNestedTable1.Table := OraQuery1.GetTable('ORDERS');
  OraNestedTable1.Active := True;
end;

And then, when changing records in the detail, you can get a level deeper to the orderlines like this using TOraNestedTable.GetObject() as TOraNestTable. You'll need to add OraObjects to your used clause for this:

procedure TForm1.OraNestedTable1AfterScroll(DataSet: TDataSet);
begin
  OraNestedTable2.Table := OraNestedTable1.GetObject('ORDERLINES') as TOraNestTable;
  OraNestedTable2.Active := True;
end;

Get Multiset field value of Oracle Query on Delphi with ODAC

1 Answers1