Home » SQL & PL/SQL » SQL & PL/SQL » describe a nested table
describe a nested table [message #183455] Fri, 21 July 2006 01:02 Go to next message
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


Re: describe a nested table [message #183490 is a reply to message #183455] Fri, 21 July 2006 02:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
My super-spidey-senses detect a rebuffed PM in the not-too-distant past. Smile
Re: describe a nested table [message #183499 is a reply to message #183490] Fri, 21 July 2006 03:18 Go to previous messageGo to next message
Frank
Messages: 7880
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)
Re: describe a nested table [message #183529 is a reply to message #183499] Fri, 21 July 2006 04:33 Go to previous messageGo to next message
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

Re: describe a nested table [message #183546 is a reply to message #183499] Fri, 21 July 2006 05:05 Go to previous messageGo to next message
Maaher
Messages: 7062
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.







http://www.orafaq.com/forum/images/message_icons/icon10.gif

MHE
Re: describe a nested table [message #183554 is a reply to message #183546] Fri, 21 July 2006 05:32 Go to previous messageGo to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
whoever knows the answer,please tell .
Re: describe a nested table [message #183583 is a reply to message #183455] Fri, 21 July 2006 07:45 Go to previous messageGo to next message
joy_division
Messages: 4640
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
Re: describe a nested table [message #184026 is a reply to message #183583] Mon, 24 July 2006 23:14 Go to previous messageGo to next message
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>
Re: describe a nested table [message #184045 is a reply to message #184026] Tue, 25 July 2006 01:35 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Go to previous messageGo to next message
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
Re: describe a nested table [message #184071 is a reply to message #184068] Tue, 25 July 2006 03:35 Go to previous message
Maaher
Messages: 7062
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
Previous Topic: Bulk Insert error - URGENT
Next Topic: Combining Collections
Goto Forum:
  


Current Time: Mon Dec 05 07:04:51 CST 2016

Total time taken to generate the page: 0.11160 seconds