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

Associative Arrays in Oracle PL/SQL: The Professional Approach

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



All the examples in this article have been tested only with Oracle 10g version 2.  I didn’t really test the examples in any of the previous Oracle versions.  I am confident enough that all of the examples should give no problems when worked with Oracle version 7.3 or above.  You can drop (or post) me a line if any of them does.  The examples can be directly copied, pasted and tested in your favorite PL/SQL environment.  They should work without any changes.

Counting the number of elements in an associative array using Oracle PL/SQL

Before getting into the professional approach, I have a small concept to introduce to you.  In my previous article (in the last section), I counted the number of elements using my own counter (which is less efficient). 

Now, I shall modify the code, which is a bit more efficient than using our own counter.  Let us start with the following code.

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

      for i in 1990..2000
            if year_sales.exists(i) then
                  tot_sales := tot_sales + year_sales(i);
                  dbms_output.put_line('Sales of ' || i || ': '
|| year_sales(i));
            end if;
      end loop;
      dbms_output.put_line('Total sales: ' || tot_sales);
      dbms_output.put_line('Avg sales: ' ||
(tot_sales/year_sales.count) );


Within the above code, I introduced a new member into the associative array called “Count.”  It is already pre-defined for you; it keeps track of the number of elements available within the same array.

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