Home arrow Oracle arrow Page 4 - Associative Arrays in Oracle PL/SQL: Introduction

Associative Arrays in Oracle PL/SQL: counting number of elements using FOR loop - Oracle

In this series of articles, we shall concentrate on working with associative arrays in Oracle PL/SQL. Even though I start with simple examples in the first article of this series, I shall introduce you (in my upcoming articles) to the power of much more advanced techniques using associative arrays with Oracle PL/SQL.

  1. Associative Arrays in Oracle PL/SQL: Introduction
  2. Associative Arrays in Oracle PL/SQL: a simple example
  3. Associative Arrays in Oracle PL/SQL: traversing consecutively using a FOR loop
  4. Associative Arrays in Oracle PL/SQL: counting number of elements using FOR loop
By: Jagadish Chatarji
Rating: starstarstarstarstar / 119
March 13, 2006

print this article



Let us extend our discussion further to count the number of elements available in the “Associative Array.”  Let us work with the following example:

      type year_type is table of number index by binary_integer;
      year_sales year_type;
      tot_sales   number := 0;
      i           number;
      c           number := 0;
      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));
                  c := c + 1;
            end if;
      end loop;
      dbms_output.put_line('Total sales: ' || tot_sales);
      dbms_output.put_line('Avg sales: ' || (tot_sales/c) );


I modified the example to include “average sales.”  The concept of “Average” works based on the “number of items” available.  How do we keep track of the “number of elements” within the “Associative Array”? 

I simply defined my own counter variable, “c,” to count the number of elements available in the “Associative Array.”  It really worked.


Are you satisfied with the techniques I implemented here?  A beginner may say YES.  But a more experienced programmer would never agree (at least to 100%) to any of the above methods.  I should also agree with them.  Why and what is the problem in working with the above methods?

In all of the previous scenarios, we should definitely know (or expect) the “least possible key” and the “highest possible key,” which is ridiculous.  And that isn't the only problem. If I have huge gaps in between “keys,” the loop gets iterated unnecessarily (degrading the performance).  This is another major disadvantage of using the above method.

My next article in this series will address all of these issues, and we shall further extend the series to a more structural, efficient and modular way of working with “Associative Arrays.”  Don’t forget to keep an eye on this website for the next article (or simply sign up for the newsletter).  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: