Home arrow Oracle arrow Database Interaction with PL/SQL: Nested Tables

Database Interaction with PL/SQL: Nested Tables

This series has been focusing on database interactions with Oracle PL/SQL. Having just covered OBJECT TYPE in Oracle and both SQL and PL/SQL ways of working with OBJECTs in the last article, we will now learn about using NESTED TABLE with OBJECT TYPES from both SQL and PL/SQL point of views. We will also discuss FOR loops with an inline SELECT statement. This article is the fifth in this series.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Nested Tables
  2. Working with DML statements using NESTED TABLE with OBJECT TYPE
  3. Working with PL/SQL using NESTED TABLE with OBJECT TYPE
  4. A Powerful FOR loop in PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 66
June 21, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Please note that all the examples in this series have been tested only with Oracle 10g.  I didn’t really test them with all the previous versions of Oracle.  I suggest you to refer the documentation of respective version you are using, if any of the programs failed to execute.

NESTED TABLE with OBJECT TYPE

Part-3 of my series covered NESTED TABLE a bit and part-4 introduced the concept of OBJECT TYPE.  Now, we shall combine those two in this article.  Let us prepare scripts from scratch to discuss them in detail.  Consider the following:

CREATE TYPE t_experience AS OBJECT
(
    CompanyName    varchar2(20),
    Position       varchar2(20),
    NoOfYears      number(2)
);
/

The above script just creates an OBJECT TYPE (which is almost similar to the one present in part-4). 

CREATE TYPE t_experience_tbl AS TABLE OF t_experience;

If we observe the above carefully, we are still creating another TYPE (not a concrete table), but it is of TABLE TYPE within database.  For our convenience, I named it as ‘t_experience_tbl’.

CREATE TABLE employees
(
    Name            varchar2(20),
   
Experiences     t_experience_tbl
)
NESTED TABLE Experiences STORE AS Experiences_tab;

The above statement creates a new table ‘employees’ (make sure to drop an old one if it exists) with only two fields,  ‘name’ and ‘experiences’.  The field ‘Experiences’ is created based on the TABLE TYPE ‘t_experience_tbl’.  This means that every employee can now store his experience list in the same row.  Indirectly, experience list itself is a table of information (CompanyName, Position, NoOfYears).  This table is being stored as a part of single row in the table ‘employees’, which is what the NESTED TABLE is.

Even though the NESTED TABLE is logically part of the ‘employees’ table, it is stored externally (with a different table name ‘experiences_tab’) from the main table.  The following statement inserts a row into that table.

insert into employees values
(
    'jag',
    t_experience_tbl
    (
        t_experience('abc company','Software Engineer',3),
        t_experience('xyz company','System Analyst',2),
        t_experience('mnp company','Research fellow',4)
     )
);

Better not confuse ourselves with the example above.  ‘t_experience_tbl’ is a TABLE TYPE based on ‘t_experience’ OBJECT TYPE.  So, ‘t_experience_tbl’ can have any number of OBJECTs of TYPE ‘t_experience’.  The same concept is practiced above.  And you can insert any number of experiences for a single row.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: