Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Nested Tables in Oracle

Re: Nested Tables in Oracle

From: FC <flavio_at_tin.it>
Date: Sun, 28 Dec 2003 09:43:03 GMT
Message-ID: <HAxHb.15857$_P.694519@news4.tin.it>

"Ritesh Kachhwaha" <r.kachhwaha_at_ntlworld.com> wrote in message news:_NzGb.896$Gc7.367_at_newsfep3-gui.server.ntli.net...
> Hello All,
>
> Can anyone please explain to me why you would need to use Nested tables
and
> give an example. Also can you show the SQL to view the table and the
nested
> table and explain the logic of it.
>
> Thanks in advance for your help.
>
>

If you have an arbitrarily long unordered sequence of values associated to each record, I find nested table very practical, especially if you ever need to copy the records from table to table, i.e. saving records in a history-like fashion. If the "values" are n-dimensional, like (x,y,z) coordinates for instance, nested tables are even more handy.

If this sequence is upper bound, then you're probably better off using varrays. They can be multidimensional too, provided you created an object type, say for instance a coordinate type (x,y,z) made up of 3 number columns, then you create a varray type basing on the custom coordinate type.

If you work on nested tables and varrays in PL/SQL, then it's strongly recommend that you create some unnesting views because there are a few annoying bugs in the PL/SQL parser that making your life hard at times. Once you have these views, then you could even dare to write INSTEAD OF triggers to manipulate the content of the nested tables.

Nested tables and varrays are a bad choice if you need to enforce relational constraints or you have to deal with a large number (>100) of "nested records" that would normally require indexes on certain columns. In that case you had better to design everything in terms of standard tables with their foreign keys, constraints, indexes and so on.

I've also experienced tricky situations with nested tables and data migration using oracle imports and exports. Never try to migrate data exported with exp into an existing table containing a nested column, the operation will most likely end with ORA-03113 "end-of-file on communication channel".

Bye,
Flavio Received on Sun Dec 28 2003 - 03:43:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US