OraFAQ Forum: SQL & PL/SQL » describe a nested table

Home » SQL & PL/SQL » SQL & PL/SQL » describe a nested table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

describe a nested table [message #183455]

Fri, 21 July 2006 01:02

guru_karnam
Messages: 142
Registered: May 2005

Senior Member

hi Maaher,

How do we describe a nested table???
Can you tell me what keywords are used for a nested table?
Explain how do we use the kewywords THE,TABLE,CAST,MULTICAST,MULTISET

RAVI

Report message to a moderator

Re: describe a nested table [message #183490 is a reply to message #183455]

Fri, 21 July 2006 02:49

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

My super-spidey-senses detect a rebuffed PM in the not-too-distant past. Smile

Report message to a moderator

Re: describe a nested table [message #183499 is a reply to message #183490]

Fri, 21 July 2006 03:18

Frank
Messages: 7901
Registered: March 2000

Senior Member

Ah, too bad.
I really really really wanted to answer this, by providing nice examples and stuff, but alas, I am not Maaher Sad

(hm, do I really regret not being Maaher? Wink

)

Report message to a moderator

Re: describe a nested table [message #183529 is a reply to message #183499]

Fri, 21 July 2006 04:33

guru_karnam
Messages: 142
Registered: May 2005

Senior Member

Hi Frank,

Yesterday Maher gave nice example and usage of save exceptions.Thatz why i asked him again!
If you have nice examples,please come out and tell .

Ravi

Report message to a moderator

Re: describe a nested table [message #183546 is a reply to message #183499]

Fri, 21 July 2006 05:05

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Frank wrote on Fri, 21 July 2006 10:18

(hm, do I really regret not being Maaher? Wink

)

I'm not going to dignify that with an answer.

MHE

Report message to a moderator

Re: describe a nested table [message #183554 is a reply to message #183546]

Fri, 21 July 2006 05:32

guru_karnam
Messages: 142
Registered: May 2005

Senior Member

whoever knows the answer,please tell .

Report message to a moderator

Re: describe a nested table [message #183583 is a reply to message #183455]

Fri, 21 July 2006 07:45

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

raviraviki wrote on Fri, 21 July 2006 02:02

hi Maaher,

How do we describe a nested table???
Can you tell me what keywords are used for a nested table?
Explain how do we use the kewywords THE,TABLE,CAST,MULTICAST,MULTISET

RAVI

These are questions easily answered by using the documentation or using a search engine such as Google. Google can be found at http://google.com

Report message to a moderator

Re: describe a nested table [message #184026 is a reply to message #183583]

Mon, 24 July 2006 23:14

h1yn
Messages: 1
Registered: July 2006

Junior Member

hi
this is example,i wish u can understand .
SQL> set autot on exp
SQL> select t.id from t,a where t.id=a.id ;

ID
----------
1
2
2

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'A'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'T'

SQL> select /*+use_nl(t,a)*/t.id from t,a where t.id=a.id ;

ID
----------
1
2
2

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=166 Card=409 Bytes=1
0634)

1 0 NESTED LOOPS (Cost=166 Card=409 Bytes=10634)
2 1 TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=82 Bytes=1066)
3 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=5 Bytes=65)

SQL>

Report message to a moderator

Re: describe a nested table [message #184045 is a reply to message #184026]

Tue, 25 July 2006 01:35

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Enough fooling around. Ravi, can you clarify what you are looking for? A describe of a nested table? What do you mean? Oracle reserved words are explained in the manuals. I'd say: check there first and then come back if you still have problems in understanding.

@h1yn: I don't know what question you are answering but I think it isn't this one.

MHE

[Updated on: Tue, 25 July 2006 01:40]

Report message to a moderator

Re: describe a nested table [message #184068 is a reply to message #184045]

Tue, 25 July 2006 03:25

guru_karnam
Messages: 142
Registered: May 2005

Senior Member

No Maaher,

I dont know how to see the values in a nested table.

When we describe the structure of a table

desc emp

we can see the structure.
But how can i describe a nested table

Report message to a moderator

Re: describe a nested table [message #184071 is a reply to message #184068]

Tue, 25 July 2006 03:35

Maaher
Messages: 7065
Registered: December 2001

Senior Member

SET DESCRIBE DEPTH _deep_enough_to_see_all_levels_ (default = 1)

My script (the example comes from here):

SET DESCRIBE DEPTH 1
CREATE TYPE phone AS OBJECT (telephone NUMBER);
/
CREATE TYPE phone_list AS TABLE OF phone;
/
CREATE TYPE my_customer AS OBJECT (
   cust_name VARCHAR2(25),
   phones phone_list);
/
CREATE TYPE customer_list AS TABLE OF my_customer;
/
CREATE TABLE business_contacts (
   company_name VARCHAR2(25),
   company_reps customer_list)
   NESTED TABLE company_reps STORE AS outer_ntab
   (NESTED TABLE phones STORE AS inner_ntab)
/

DESC business_contacts
PROMPT We set the describe deeper:
SET DESCRIBE DEPTH 2
DESC business_contacts

SET DESCRIBE DEPTH 3
DESC business_contacts


DROP TABLE business_contacts
/
DROP TYPE customer_list
/
DROP TYPE my_customer
/
DROP TYPE phone_list
/
DROP TYPE phone
/

Run this one and you'll see this:

SQL> @orafaq

Type created.


Type created.


Type created.


Type created.


Table created.

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------

 COMPANY_NAME                                       VARCHAR2(25)
 COMPANY_REPS                                       CUSTOMER_LIST

We set the describe deeper:
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------

 COMPANY_NAME                                       VARCHAR2(25)
 COMPANY_REPS                                       CUSTOMER_LIST
   CUST_NAME                                        VARCHAR2(25)
   PHONES                                           PHONE_LIST

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------

 COMPANY_NAME                                       VARCHAR2(25)
 COMPANY_REPS                                       CUSTOMER_LIST
   CUST_NAME                                        VARCHAR2(25)
   PHONES                                           PHONE_LIST
     TELEPHONE                                      NUMBER


Table dropped.


Type dropped.


Type dropped.


Type dropped.


Type dropped.

SQL>

MHE

Report message to a moderator

Previous Topic:	Bulk Insert error - URGENT
Next Topic:	Combining Collections

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 10:45:52 CST 2024