Exforsys.com
 
Home Tutorials Oracle 9i
 

Tutorial 12: Oracle 9i: PL/SQL Collections

 

Tutorial 12: Oracle 9i: PL/SQL Collections - Page 2

Page 2 of 2



Creating Nested Tables

The following example explain how to create a table type and then nest it in a table





Following syntax is used to display columns of the above table we write.

SQL > SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS;
SQL > SELECT TYPECODE, ATTRIBUTES FROM USER_TYPES;

TYPECODE ATTRIBUTES
------------------------------ ----------
OBJECT 2
COLLECTION 0

To insert values into the above table we use the below given syntax



To query a nested table Oracle provides a new function THE. To select data from a field in the nested table we must have to flatten the table first. Function THE is used for this purpose.


{mosgoogle)




Varrays:


Is short form for variable sized arrays. They are used to store repeating attributes in a table. Varying arrays can be created based on standard datatypes of Oracle or by using abstract datatypes.
A varying array take fixed number of elements when it is created, which can be changed at runtime. The subscript used by Varrays is sequential. We have to decide the maximum number of elements for the varying array.


Creating VARRAYS


Below given example explains creation and usage of VARRYS. Let us consider an organization developing multiple projects simultaneously. Each project has few team members and these members report to a team leader. We will have a varying array for storing members’ names. Another table is created for storing the project details.



To describe table we use


SQL > DESC PROJECT
Name Null? Type
----------------------------------------- -------- ----------------
PROJID NUMBER (5)
PROJNAME VARCHAR2 (25)
TEAMLEAD VARCHAR2 (20)
TEAMMEMBERS MEMBERS_VA

We use the following query to see the datatype of MEMBERS_VA




Inserting Records into Varying Arrays.




Associative arrays (or) Index-by tables:


They are sets of key-value pairs. The keys are maintained unique. The key can be either an integer or a string. The unique key is created using primary key from a table. The types raw , longraw , rowid etc are not allowed as keys.


Creating Associative Arrays





First Page: Tutorial 12: Oracle 9i: PL/SQL Collections


Read Next: Tutorial 13: Oracle 9i : Procedures and Functions



 

 

Comments


Abdul Rahman said:

  Oracle 9i Collections Made Easy, Excellent Collection of Easy Reference Stuff. Keep up the great work Exforsys Team.Best of luck :)
August 4, 2006, 2:08 am

Radhika V said:

  Excellent Work. You could also discuss other dml commands on varrays such as updating, deleting and accessing each and every element of varray :)
August 11, 2006, 3:14 am

itanand said:

  Excellent work!! :-*
September 20, 2006, 2:36 am

harishb said:

  how to handle the nulls in oracle?
December 11, 2006, 2:35 am

Imtiaz ali said:

  Associate arrays or Index-by table is not understandable. This is the only topic found difficult to understand otherwise complete tutorial is fine.
May 30, 2007, 9:49 pm

arulkumar said:

  i'm beginner to oracle .it ll be very useful for beginners like me
August 16, 2007, 11:31 pm

bharat kumar said:

  im a beginner of oracle and these sessions are really great for people like me to get about basics
November 27, 2007, 6:44 am

eric said:

  this article is easy to follow except the "Associative arrays (or) Index-by tables" part.

August 11, 2008, 4:25 am

eric said:

  this article is perfect for beginners
August 11, 2008, 4:29 am

Abiash said:

  Excellent tutorial. i don't know what to say other than thank you very much for this amazing informative section.

November 12, 2008, 2:17 am

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2009 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape