Nested tables / collections

[Bsil]

Hi,

If I make a query with a TOracleDataset (needed for linking to grid, so can't use TOracleQuery) which contains a nested table
or a collection ( ex. select * from the demo view oe.oc_orders, that comes with oracle 11g) - the view is defines as:
SELECT o.order_id
, o.order_mode
,MAKE_REF(oc_customers,o.customer_id)
,o.order_status
,o.order_total
,o.sales_rep_id
,CAST(MULTISET
(SELECT l.order_id
,l.line_item_id
,l.unit_price
,l.quantity
,make_ref(oc_product_information,l.product_id)
FROM order_items l
WHERE o.order_id = l.order_id)
AS order_item_list_typ)
FROM orders o

Well here I have a problem with the "order_item_list_typ" a nested table/collection. The TOracleDataset does not make a FieldDef for it, so I can't detect that the field is in the dataset and take appropriate action - which leads to another question!

Can I make a client dataset for that "field", i.e. master/client like setup?

Best regards

Bj

 

[Marco Kalter]

Yes. To quote the User's Guide:

Nested tables and varrays
If the table of the master dataset contains a collection column or attribute (nested table or varray), you can create a detail dataset for this collection. At design time you do not need to define the SQL for the detail dataset. Simply set the master property and select a collection column or attribute from the selection screen. After doing so, the SQL of the detail dataset will be something like this:

select d.*
from table(select m.lines
           from   invoices m
           where  m.id = :id) d

In this case the master table is invoices, the collection column is lines, and the primary key column of the master table is id. The basic SQL that is generated by the Automatic Master/Detaul configuration can of course be refined by specifying specific columns instead of d.*, by adding an order by clause, and so on.

 

[Bsil]

Hi Marco,
Thanks for the answer - Yes I found the answer to the second part in the users guide as well

But you didn't answer the first part.

I have an application, where I don't know the query, so grid columns will be created at runtime, and I need a way to detect the presence of a nested table in the result set (TOracleDataset). At present, I can't fins a way to detect this, as no "FieldDef" is created for object fields in The TOracleDataset component, so looping through the "FieldDefs" won't do me any good in this instance.

The only way I can find is to issue a "describe" command before executing the query, and then handle the Datatype (108) separately - cumbersome, but doable.

So is there another way of doing this?

------------------------------------------

And just to be annoying - here is one more...

The example given in my first entry return a REF column, is there an easy way of using this ref in a TOracleDataset (same as with the nested table) to make a master/detail query, and not going through the motion of creating a RefFiled and pinning the object to iterating through "attributes" ?

Best regards
Bj

 

[Marco Kalter]

The only way I can find is to issue a "describe" command before executing the query, and then handle the Datatype (108) separately - cumbersome, but doable.

I think this is the only way to do it.

The example given in my first entry return a REF column, is there an easy way of using this ref in a TOracleDataset (same as with the nested table) to make a master/detail query, and not going through the motion of creating a RefFiled and pinning the object to iterating through "attributes" ?

No, there is no built-in functionality for ref columns.