O'Reilly Databases

oreilly.comSafari Books Online.Conferences.

We've expanded our coverage and improved our search! Search for all things Database across O'Reilly!

Search Search Tips

advertisement
AddThis Social Bookmark Button

Print Subscribe to Databases Subscribe to Newsletters
Oracle PL/SQL Programming, 3rd Edition

Oracle 9i Release 2 Developments for PL/SQL Collections

by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn
04/01/2003

Editor's note: In the previous article in their continuing series on new Oracle 9i features, Steven Feuerstein and Bryn Llewellyn looked at Utl_Http and showed how you can use it in an Oracle 9i database to implement a requestor in a B2B implementation. In this article, the pair explores PL/SQL collections in Oracle 9i Release 2, with particular emphasis on associative arrays. Find out why extending the flexibility of the collection syntax, storage, and access, makes it possible to write much simpler, more efficient code than was possible in earlier versions.

Introduction

For the first several years after Steven published the first edition of Oracle PL/SQL Programming in 1995, he evangelized the use of packages as a fundamental building block of PL/SQL-based applications. This seemed to be a critical message for a number of years, as relatively few developers knew about and used packages. Lately, it appears that the "package story" has caught on; most developers do deploy the majority of their functionality from within packages. In the course of querying students and presentation attendees about their programming habits, however, Steven has discovered another very helpful aspect of PL/SQL that's being drastically under-utilized: the collection.

A PL/SQL collection is, in its essence, very similar to a single-dimensioned array. A collection allows you to maintain lists of information, and it can be used to improve query performance and also simplify the code you write to manage data within a PL/SQL program. These collection data structures come in three flavors: nested table, varying array (a.k.a. VARRAY), and associative array. We can't, within the scope of this article, offer a complete introduction to collections. You can obtain such coverage from any number of PL/SQL texts and the Oracle documentation. Our intention in this article is, instead, to let you know about some very interesting developments in Oracle 9i Release 2 regarding PL/SQL collections.

Related Reading

Oracle PL/SQL Programming
By Steven Feuerstein

We will, in fact, focus on one particular type of collection: the associative array. For those of you who have worked with collections over the years, this will be an unfamiliar term. Some of you will remember back in Oracle 7 when collections were first introduced; at that point, they were called "PL/SQL tables," since they were similar to very simple relational tables (consisting of a single column) but were declared and manipulated only within PL/SQL programs. Then in Oracle 8, Oracle introduced two other kinds of single- dimensioned lists (VARRAYS and nested tables). In the process, they changed the name of the original collection type from "PL/SQL table" to "index-by table" (reflecting its mandatory INDEX BY BINARY_INTEGER clause).

VARRAYS and nested tables can be used both in schema-level declarations (especially, for example, for the type of a column of a relational table) and in PL/SQL declarations. Index-by tables can be used only in PL/SQL declarations. Now, in Oracle 9i Release 2, Oracle has once again renamed this collection type, this time from "index-by table" to "associative array." Why another change? The term associative array is the name commonly used in other programming languages (including Perl, C++, JavaScript, and Cymbal, to name a few) to refer to a data structure that stores pairs of keys and values. By making this change, PL/SQL becomes more consistent with the nomenclature of much of the programming world, and therefore makes PL/SQL a bit more accessible to developers who are new to PL/SQL, but have experience in other languages.

Associative arrays can still be used only in PL/SQL declarations. The wonderful development regarding collections in Oracle 9i Release 2 is, however, not in the name change, but in some significant new functionality. Let's take a look.

Declaring Associative Arrays

In the bad old days, there was just one way to declare an associative array:

DECLARE
   TYPE names_list_t IS
      TABLE OF employee.last_name%TYPE
      INDEX BY BINARY_INTEGER;

The "INDEX BY BINARY_INTEGER" clause was fixed and unchangeable. This meant that the only index allowed on an associative array was the row number, and the row number had to have been declared as BINARY_INTEGER.

There were several drawbacks to this scheme. First, it required reliance on an outmoded datatype, since BINARY_INTEGER has since been superseded by PLS_INTEGER as a more efficient integer datatype. Second, it meant that if the list being manipulated had a non-integer key, the developer had to write some very complex and/or compute-intensive logic (namely, to perform full collection scans or create alternative indexes via hashing) to take advantage of collections.

These restrictions have now been lifted. You can now declare associative arrays to be indexed by BINARY_INTEGER, PLS_INTEGER, VARCHAR2 and even anchored declarations of those types using %TYPE. All of the following statements are valid declarations of associative array types with integer indexes:

DECLARE  
   TYPE array_t1 IS 
      TABLE OF NUMBER 
      INDEX BY BINARY_INTEGER;
        
   TYPE array_t2 IS 
      TABLE OF NUMBER 
      INDEX BY PLS_INTEGER;

   TYPE array_t3 IS 
      TABLE OF NUMBER 
      INDEX BY POSITIVE;

   TYPE array_t4 IS 
      TABLE OF NUMBER 
      INDEX BY NATURAL;
BEGIN
   ...
END;

And, very interestingly, if you do declare a type based on a constrained BINARY_INTEGER subtype, such as POSITIVE, then, if you try to reference a negative row number, you'll get an error, as shown here:

SQL> DECLARE     
  2     TYPE pos_only_t IS 
  3        TABLE OF NUMBER 
  4     INDEX BY POSITIVE;
  5  
  6     pos_only pos_only_t;
  7  BEGIN
  8     pos_only (-9) := 1;
  9  END;
 10  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8

Note: Even at Oracle 8i Version 8.1.7, the syntax to use subtypes of BINARY_INTEGER for an index-by table was allowed, but the implied constraint wasn't enforced.

You can even use a user-defined subtype, thus:

DECLARE
   SUBTYPE my_integer IS PLS_INTEGER NOT NULL;
   TYPE array_t4 IS
      TABLE OF NUMBER
   INDEX BY my_integer;
BEGIN
  ...
END; 

So PL/SQL is now much more flexible than it used to be when it comes to declaring integer-indexed collections. (Note: There are still restrictions. See the section titled "Invalid declarations" for reminders of which syntax is still not deemed acceptable.)

Much more exciting, however, is the fact that you can now declare associative arrays to have VARCHAR2 or string index values! Here are some examples of such declarations:

DECLARE     
  TYPE array_t1 IS 
     TABLE OF NUMBER 
     INDEX BY VARCHAR2(64);

  TYPE array_t3 IS 
     TABLE OF NUMBER 
     INDEX BY VARCHAR2(32767);
                          
  TYPE array_t4 IS 
     TABLE OF NUMBER 
     INDEX BY employee.last_name%TYPE;
BEGIN
  ...
END;
/

It's especially impressive that Oracle now lets us use %TYPE to declare an associative array with a VARCHAR2 index. This allows us to avoid hard-coding a VARCHAR2 maximum length in the TYPE statement.

Pages: 1, 2, 3, 4

Next Pagearrow




Tagged Articles

Be the first to post this article to del.icio.us

Sponsored Resources

  • Inside Lightroom

Related to this Article

Big Data Now: Current Perspectives from O'Reilly Radar Big Data Now: Current Perspectives from O'Reilly Radar
by O'Reilly Radar Team
August 2011
$0.00 USD

Professional NoSQL Professional NoSQL
August 2011
$29.99 USD

Advertisement
Sign up today to receive special discounts,
product alerts, and news from O'Reilly.
Privacy Policy >
View Sample Newsletter >
  • Youtube
  • http://www.youtube.com/OreillyMedia
  • Twitter
  • Subscribe
  • View All RSS Feeds >
O'Reilly Media

800-889-8969 or 707-827-7019
Monday-Friday 7:30am-5pm PT
©2011, O'Reilly Media, Inc.
All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.
  • About O'Reilly
  • Academic Solutions
  • Contacts
  • Customer Service
  • Careers
  • Press Room
  • Privacy Policy
  • Terms of Service
  • Writing for O'Reilly
  • Community
  • Authors
  • Forums
  • Membership
  • Newsletters
  • RSS Feeds
  • User Groups
  • More O'Reilly Sites
  • igniteshow.com
  • makerfaire.com
  • makezine.com
  • craftzine.com
  • labs.oreilly.com
  • Partner Sites
  • PayPal Developer Zone
  • O'Reilly Insights on Forbes.com