Home arrow Oracle arrow Page 5 - Database Interaction with PL/SQL, part 3

Using DML Commands on NESTED TABLES Within PL/SQL - Oracle

Jagadish Chatarji has been writing about database interactions with Oracle PL/SQL. The last part started on TYPE, RECORD, and TABLE declarations of PL/SQL. This one now goes further into TABLE, RECORD, and using them together. It will also introduce NESTED TABLES.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, part 3
  2. Using TABLE Without Interacting With Database
  3. Combining TABLE and RECORD
  4. NESTED TABLES and PL/SQL
  5. Using DML Commands on NESTED TABLES Within PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 18
June 07, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Just like in SQL, we can also insert, update or delete NESTED TABLE information from within PL/SQL, but using typed variables. Let us consider the following example which updates a row in the same table.

declare
   
v_subjects SubjectList;
   
v_name employee.name%type := '&name';
begin
   
v_subjects := SubjectList('all databases','all languages');
   
update employee set Subjects = v_subjects
   
where name=v_name;
   
dbms_output.put_line('Updated succesfully...');
end;

I hope the example above is very much clear. And even in the same manner we can also insert into the same table something like the following:

declare
   
v_subjects SubjectList;
   
v_name employee.name%type := '&name';
begin
   
v_subjects := SubjectList('all databases','all languages');
   
insert into employee values(v_name, v_subjects);
   
dbms_output.put_line('inserted succesfully...');
end;

Actually, it is bit difficult to do some kinds of DML operations on NESTED TABLEs (and on the individual elements within the NESTED TABLE especially). Though I am not covering much of the SQL here, we can overcome the complexity with vigorous practice of SQL.



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

blog comments powered by Disqus
   

ORACLE ARTICLES

- 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
- Using the IN and BETWEEN Operators on Tables
- Clauses and Logical Operators for Retrieving...
- Limiting Rows When Retrieving Table Data
- Using Scalar Functions for Retrieving Data
- Retrieving Data with String and Arithmatic E...
- Coding the SELECT Statement
- Oracle Releases iPad Virtual Desktop and Exa...


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 9 - Follow our Sitemap

Dev Shed Tutorial Topics: