Re: Nested Tables in Oracle

On Thu, 25 Dec 2003 11:23:11 -0000, "Ritesh Kachhwaha" <r.kachhwaha_at_ntlworld.com> wrote:

>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.

I think nested tables are most useful in combination with PL/SQL, because PL/SQL has a nested table datatype that you can use for variables. So you can set up a PL/SQL nested table variable with an arbitrary number of elements, then insert the whole thing as part of a table row.

See:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm#1059

Think of a table where a row represents a person, and that person has a set of favourites. You could set up a second table where each favourite had its own row that included the identifier for the person. Or you could make one of the columns in the "person" table be a nested table.

If you were doing everything in SQL, joining the two tables might be convenient whenever you wanted to find all the favourites for a particular person. If you were working more in PL/SQL, the nested table might be more convenient since you could pull all the favourites into a nested table variable, then pass that variable to PL/SQL stored procedures and functions, delete item # N, and other PL/SQL-style operations.

John

--
Photo gallery: http://www.pbase.com/john_russell/