Re: And again: 1NF may not be dead

Dawn M. Wolthuis wrote:
> Do you know what the output is in Oracle if you select * from t
> if you have both this relation and another nested in t?
> In other words, is there a select that could give you ouput like
this:
>
> John Doe (123) 555-1234 jdoe_at_msn.com
> (232) 555-2837 john_doe_at_aol.com
> jd_at_johndoe.org
>

I'm not very au fait with using "nested tables" in Oracle tables, mainly because I don't really approve of them. But here goes:

SQL> create table t (name varchar2(10), aliases name_tab_type, emails name_tab_type )
2 nested table aliases store as aliases_tab 3 nested table emails store as emails_tab 4 /

Table created.

SQL> insert into t values ('John',
name_tab_type('Johnny','JJ','Fingers'),
2 name_tab_type('John_at_x.com','JJ_at_y.com'));

1 row created.

SQL> select * from t;

NAME       ALIASES                                  EMAILS
---------- ----------------------------------------
----------------------------------------
John       NAME_TAB_TYPE('Johnny', 'JJ', 'Fingers')

That result does look exactly like yours, but it is logically equivalent I think. This is just how Oracle's SQL Plus tool happens to display the result: you could write your own tool (or perhaps 3rd party tools already exist) to display the results differently. Received on Thu Oct 28 2004 - 12:37:57 CEST