QUERY problem with objects and nested tables in Oracle8

Hello,

I'm experimenting with the conversion of a Dijkstra matrix flat-file to an efficient Dijkstra table in Oracle8 using objects. Building it all up was not too hard, but I have serious problems with querying it !

This is how its build up (the actual problem is stated at the end:

• I first create an object type which can hold a node-distance pair CREATE TYPE "DIJK"."DIJKDISTANCE" AS OBJECT ( endnode NUMBER(8), distance NUMBER(9));
• then I create a tabletype that can hold objects of type DIJKDISTANCE create type dijkdistance_table_type as table of dijkdistance;
• now I create the table that has only 2 colums: the beginning node end a nested table of objects that hold
• the ending node and the distance (in time) between the beginning and the ending node. create table dijkstra( beginnode number(8), dijkdistances dijkafstand_table_type) nested table dijkafstanden store as nested_dijkdistancetable;
• create a primary key alter table dijkstra add constraint PK_DIJKSTRA primary key(beginnode) using index tablespace ndx;
• create an index on the ending node within the object create index IDX_PARTNERNODE on nested_dijkdistancetable (endnode);

  ---

Up until now no problems, .., in the above way I simulate a 2-dimensional flatfile in a database table
with a nested table. I populated it, .., but now I want to query it !

I want to select exactly 1 object from the table from which I can extract the 'distance' value between
the 2 nodes. I'm quite confidend that I need the 'THE' operator and that I have to use the beginning and
the ending node as constraints in the query in the where clause.

It seems to be a very simple problem, ..., but the more I get to read the more I'm getting afraid that
this isn't possible yet ! (its my personal opinion that if this isn't possible then Oracle shouldn't claim
that they have implemented objects and nested tables, I hope this will not be the case). The books
are very very brief with their information regarding the combination of nested tables and objects and
selecting atomary object from those nested tables.

I hope anyone can help me with this one.

regards,
Stefan Gyles. Received on Sat Dec 26 1998 - 00:00:00 CST