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

Working with DML statements using NESTED TABLE with OBJECT TYPE - Oracle

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

The following SELECT statement gives the respective details of NESTED TABLE available in a single row of parent table:

select a.CompanyName,a.NoOfYears from
table(select experiences from employees where name='jag') a

You can also replace the word ‘table’ with another keyword ‘the’ as follows:

select a.CompanyName,a.NoOfYears from
the(select experiences from employees where name='jag') a

Any of the above two examples performs two operations.  First, it reads an ‘employees’ row with the given name.  Then, it reads the elements stored in that row’s nested table.  For better readability, I used ‘table’ instead of ‘the’.  You can even replace the field names with ‘*’ as follows:

select a.* from
table(select experiences from employees where name='jag') a

By now, we already know how to retrieve information from NESTED TABLEs.  Now we need to know how to insert rows to the NESTED TABLE (not to the main table ‘employees’)?  This means I wanted to add another ‘experience’ row to the same employee ‘jag’.  The following statement does it.

INSERT INTO
TABLE(SELECT experiences FROM employees WHERE name = 'jag')
VALUES
(
   
t_experience('efg company','Professor',2)
);

If you compare the above example with previous SELECT statements, you should be able to understand and there is nothing new from any angle.  Actually it is not necessary to use ‘t_experience’ in the above statement, you can also insert it as follows:

INSERT INTO
TABLE(SELECT experiences FROM employees WHERE name = 'jag')
VALUES
(
    
'efg company','Professor',2
);

But for better readability, I suggest you to use the TYPE name along with values.  You can even modify the existing rows using UPDATE statement as follows:

UPDATE
TABLE(SELECT experiences FROM employees WHERE name = 'jag')
set NoOfYears=5
where CompanyName='abc company';

You can even use DELETE statement on NESTED TABLE as follows:

DELETE FROM
TABLE(SELECT experiences FROM employees WHERE name = 'jag')
where CompanyName='abc company';

If you don’t use any OBJECT type in the NESTED TABLE (like a NESTED TABLE of varchar2, like in part-3), you can use a keyword ‘column_value’ for UPDATE or DELETE statements.  It is necessary because the NESTED TABLE does not have any specific columns to address.



 
 
>>> 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: