Home arrow Oracle arrow Page 5 - Associative Arrays in Oracle PL/SQL: The Professional Approach

Deleting a set of elements in an associative array using Oracle PL/SQL - Oracle

This is the second article in a series focusing on associative arrays. In this article, we shall examine recommended methodologies for working with associative arrays efficiently.

  1. Associative Arrays in Oracle PL/SQL: The Professional Approach
  2. A professional approach to traversing an associative array using Oracle PL/SQL
  3. Bottom to top traverse of an associative array using Oracle PL/SQL
  4. Deleting individual elements in an associative array using Oracle PL/SQL
  5. Deleting a set of elements in an associative array using Oracle PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 22
March 20, 2006

print this article



In the previous section, I deleted only individual elements.  In this section, I shall introduce you to deleting a series of elements (pairs) in an associate array.

Let us consider the following code:

      type year_type is table of number index by binary_integer;
      year_profits      year_type;
      tot_profits number := 0;
      i           number;
      year_profits(1990) := 34000;
      year_profits(1991) := 45000;
      year_profits(1992) := 43000;
      year_profits(1996) := 13000;
      year_profits(1998) := 53000;


      i := year_profits.first;
      while i <= year_profits.last
            tot_profits := tot_profits + year_profits(i);
            i := year_profits.next(i);
      end loop;

      dbms_output.put_line('Total profits: ' || tot_profits);
      dbms_output.put_line('No. of years excluding years between
1991 and 1995: ' || year_profits.count);


Within the above code, you can understand that it removes all the elements (or pairs) from the associative array, which are in between the keys 1991 and 1995 (both inclusive).


Until now, I focused on using only BINARY_INTEGER based “keys” for the pairs that are going to be stored in the associative array.  In fact, you can also try using all of these methods with VARCHAR2.

When you work with a VARCHAR2 based indexed, it works very similar to a hash table.  If we embed the whole code in a beautiful package, I am quite sure that it would help you a lot.  We can further extend this with CURSORS, REF CURSORS, OBJECT TYPES, VARRAYS and so on.  Even though I concentrated only on  associative arrays, several of these methods should work with other types of PL/SQL tables as well.  Go ahead and try it. 

In the next article in this series, I shall mix all these methods in a modular fashion and finally implement it in a reusable package.  So be sure to check this website frequently! 

Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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